GET_JSON_OBJECT

Description

The GET_JSON_OBJECT function is used to extract the value of a specific field from a JSON-formatted string (str). It accepts two parameters: the first parameter is a JSON-formatted string, and the second parameter is a string used to specify the path of the field to be extracted in the JSON object.

Parameters

• str (string): A string containing JSON-formatted data.
• path (string): A string path describing the location of the field to be extracted. Refer to the json path specification
  • "$" represents the root element
  • ".key" or "['key']" is used to find the key in the JSON object. Specially, "[*]" means to get all values, and it must be enclosed in single quotes
  • "[index]" is used to access elements of a JSON array by index, starting from 0. Specially, "[*]" means all elements

Return Value

• Returns the value of the specified field, type is string.

Example Usage

1. Suppose we have a JSON object as follows:

{
  "name": "Zhang San",
  "age": 30,
  "address": {
    "city": "Beijing",
    "zipcode": "100000"
  },
  "hobbies": ["basketball", "traveling", "reading"]
}

We can use the GET_JSON_OBJECT function to extract different fields from a JSON object, for example:

• Extract the name:

SELECT get_json_object('{"name": "张三", "age": 30, "address": {"city": "北京", "zipcode": "100000"}, "hobbies": ["篮球", "旅游", "阅读"]}', '$.name');

Results:

• Extracted city:

SELECT get_json_object('{"name": "张三", "age": 30, "address": {"city": "北京", "zipcode": "100000"}, "hobbies": ["篮球", "旅游", "阅读"]}', '$.address.city');

Result: Beijing

• Extract all hobbies:

SELECT get_json_object('{"name": "张三", "age": 30, "address": {"city": "北京", "zipcode": "100000"}, "hobbies": ["篮球", "旅游", "阅读"]}', '$.hobbies');

Result: ["篮球", "旅游", "阅读"]

2. Another example, we have a JSON array as follows:

[
  {
    "id": 1,
    "name": "Product A",
    "price": 100
  },
  {
    "id": 2,
    "name": "Product B",
    "price": 200
  }
]

We can use the GET_JSON_OBJECT function to extract specific elements from an array, for example:

• Extract the price of the first product:

SELECT get_json_object('[{"id": 1, "name": "产品A", "price": 100}, {"id": 2, "name": "产品B", "price": 200}]', '$[0].price');

Result: 100

• Extract the names of all products:

SELECT get_json_object('[{"id": 1, "name": "产品A", "price": 100}, {"id": 2, "name": "产品B", "price": 200}]', '$[*].name');

Result：["产品A", "产品B"]

Through the above example, you can see the flexibility and practicality of the GET_JSON_OBJECT function when handling JSON data. In practical applications, you can freely combine path strings as needed to extract any field from the JSON data.