Overview

To ensure correct JSON formatting:

{
   "code":"o"       // Incorrect JSON string
}
{
   "code":true      // Correct JSON string
}
{
   "code":"o"       // Correct JSON string
}
{
   "code":{
      "code_1":"o_1",
      "code_2":"o_2"
   }                // Correct JSON string
}
{
   "code":"{\"code_1\":\"o_1\",\"code_2\":\"o_2\"}" // Incorrect JSON string (double quotes misplaced)
}
{
   "code":[
      {
         "code_1":"o_1",
         "code_2":"o_2"
      },
      {
         "code_3":"o_3",
         "code_4":"o_4"
      }
   ]                 // Correct JSON string (contains JSON array)
}

String to JSON

Using from_json Function

• Syntax:

  from_json(jsonStr, schema [, options])  -- Converts JSON string to JSON object based on specified schema string

• Example 1:

  select from_json('[{"text":"Tea"},{"text":"Apple"}]', 'ARRAY<STRUCT<text: STRING>>');  -- Output: [{"text":"Tea"},{"text":"Apple"}]

• Example 2:

  event_action='display_list' and page_type = 0 and array_contains(from_json(params['templates'],'STRUCT<id: ARRAY<INT>>').id, 30)

to_json Function

The to_json function expects input that is a struct, struct array, map, or map array. It cannot directly convert a string to JSON type.

Extracting Fields from String to JSON

If JSON data is stored as a string in a table, Hive provides functions to retrieve the value of a key from the JSON string. These functions are:

• get_json_object(jsonStr, path): Retrieves the value of a single key from a JSON string. The result is a string.
• json_tuple(jsonStr, key1, key2, ...): Retrieves the values of multiple keys from a JSON string. The result is a string.

Additionally, when loading JSON data into a table, Hive can parse all fields and transform them into a tabular form.

• Method 1: Use the built-in JSON parser Jsonserde.
• Method 2: Use the to_json(str) function.

Therefore:

If you need to use all fields of the JSON, use the built-in JSON parser Jsonserde.

If you need to use only some fields of the JSON, use the get_json_object(jsonStr, path) and json_tuple(jsonStr, key1, key2, ...) functions.

get_json_object()

(1) Overview

• get_json_object() is a UDF, meaning it takes one row of data as input and outputs one row of data. The output is a string type, so it often needs to be nested.
• Limitation: Can only retrieve the value of a single key from a JSON string at a time.
• Feature: If the entire JSON data is stored as a string type in the table, this function can be used to retrieve the value. Note that both keys and values in the JSON string must be enclosed in double quotes; otherwise, they will not be parsed correctly.

(2) Syntax

Refer to the documentation: Link

① Extracting Single JSON Object

Assuming there is an Hive table named employees containing employee information, where the employee column is a JSON string with the following structure:

'{
   "id": 1,
   "name": "John Doe",
   "salary": 5000,
   "skills": ["Java", "Python", "SQL"],
   "address": {
      "street": "123 Main St",
      "city": "New York",
      "state": "NY"
   }
}'

• Retrieve a property value:

  SELECT get_json_object(employee, "$.name") AS name FROM employees

• Retrieve an array element:

  SELECT get_json_object(employee, "$.skills[0]") AS name FROM employees

• Retrieve nested property value with string nesting:

  SELECT get_json_object(employee, "$.address.street") AS name FROM employees

• Determine if a property value exists:

  SELECT if(get_json_object(employee, "$.id") is null, 'Property does not exist', 'Property exists')