JSON functions

JSON_ARRAY_CONTAINS

Syntax

json_array_contains(json,value)

Description

Determines whether a value specified by value is included in a JSON array.

Return value type

BOOLEAN

Example

  select json_array_contains('[1, 2, 3]', 2);
  +-----------------------------------------+
  |                    1                    |

JSON_ARRAY_GET

Syntax

json_array_get(json_array,index)

Description

Returns a specified element in a JSON array based on the value of index.

• If the value of index is greater than or equal to 0, elements are returned from the beginning of a JSON array.

• If the value of index is less than 0, elements are returned from the end of a JSON array.

• If the element specified by index does not exist, NULL is returned.

Return value type

VARCHAR

Example

  select json_array_get('["a", "b", "c"]', 1) as result;
  +-----------------------------------------------+
  |                       b                       |

JSON_ARRAY_LENGTH

Syntax

json_array_length(json)

Description

Returns the length of a JSON array.

Return value type

BIGINT

Example

  select json_array_length('[1, 2, 3]')
  +-----------------------------------+
  |                 3                 |

JSON_EXTRACT

Syntax

json_extract(json,json_path)

Description

Returns the value specified by json_path from a JSON object. The value of json_path is in the format of $[n].

• If the value of n is less than 0, an error is returned.

• If the value of n is an invalid value that is greater than 0, NULL is returned.

Return value type

JSON

Example

  select json_extract('[10, 20, [30, 40]]', '$[1]');
  +------------------------------------------------+
  |                      20                        |

JSON_EXTRACT_SCALAR

Syntax

json_extract_scalar(json,json_path)

Description

Returns the scalar value specified by json_path from a JSON object. The value of json_path is in the format of $[n].

• If the value of n is less than 0, an error is returned.

• If the value of n is an invalid value that is greater than 0, NULL is returned.

Return value type

VARCHAR

Examples

  select json_extract_scalar('[10, 20, [30, 40]]', '$[1]');
  +------------------------------------------------------+
  |                        20                            |

  select json_extract_scalar('[10, 20, [30, 40]]', '$[0]');
  +------------------------------------------------------+
  |                        10                            |

JSON_ARRAY_EXTRACT/JSON_ARRAY_EXTRACT_SCALAR

Syntax

json_array_extract(json,jsonPath)

Description

Returns the value specified by json_path from a JSON array.

Return value type

VARCHAR (JSON array of strings)

Examples

  select json_array_extract(arr1, '$.book.id') as a 
  from (values ('[{"book":{"id":"12"}}, {"book":{"id":"14"}}]')) t(arr1);

["12", "14"] is returned.

  select json_array_extract_scalar(arr1, '$.book.id') as a 
  from (values ('[{"book":{"id":"12"}}, {"book":{"id":"14"}}]')) t(arr1);

[12, 14] is returned.

JSON_FORMAT

Syntax

json_format(json)

Return value type

VARCHAR

Example

  select json_format(JSON '[1, 2, 3]');
  +-----------------------------------+
  |             [1,2,3]               |

JSON_PARSE

Syntax

json_parse(string)

Description

Parses a string as JSON data and returns the data.

Return value type

JSON

Examples

  select json_parse('[1, 2, 3]');
  +----------------------------+
  |           [1,2,3]          |

  select json_parse('"a"');
  +-----------------------+
  |           "a"         |

JSON_SIZE

Syntax

json_size(json,json_path)

Description

Returns the size of a JSON object.

Examples

  select json_size('{"x":{"a":1, "b": 2}}', '$.x') as result;
  +---------------------------------------------------------+
  |                            2                            |

  json_size('{"x": {"a": 1, "b": 2}}', '$.x.a') as result; 
  +------------------------------------------------------+
  |                          0                           |

JSON_REMOVE

Syntax

json_remove(json_string,json_path_string)
json_remove(json_string,array[json_path_string])

Description

Removes a specified JSONPath expression string from a JSON string and returns the remaining data of the JSON string. You can use array[json_path_string] to specify multiple JSONPath expression strings at a time.

Return value type

VARCHAR (JSON string)

Examples

  select json_remove(
  '{
      "glossary": {
          "title": "example glossary",
          "GlossDiv": {
              "title": "S",
              "GlossList": {
                  "GlossEntry": {
                      "ID": "SGML",
                      "SortAs": "SGML",
                      "GlossTerm": "Standard Generalized Markup Language",
                      "Acronym": "SGML",
                      "Abbrev": "ISO 8879:1986",
                      "GlossDef": {
                          "para": "A meta-markup language, used to create markup languages such as DocBook.",
                          "GlossSeeAlso": ["GML", "XML"]
                      },
                      "GlossSee": "markup"
                  }
              }
          }
      }
  }'
  , '$.glossary.GlossDiv') a;

{"glossary":{"title":"example glossary"}} is returned.

  select json_remove(
  '{
      "glossary": {
          "title": "example glossary",
          "GlossDiv": {
              "title": "S",
              "GlossList": {
                  "GlossEntry": {
                      "ID": "SGML",
                      "SortAs": "SGML",
                      "GlossTerm": "Standard Generalized Markup Language",
                      "Acronym": "SGML",
                      "Abbrev": "ISO 8879:1986",
                      "GlossDef": {
                          "para": "A meta-markup language, used to create markup languages such as DocBook.",
                          "GlossSeeAlso": ["GML", "XML"]
                      },
                      "GlossSee": "markup"
                  }
              }
          }
      }
  }'
  , array['$.glossary.title', '$.glossary.GlossDiv.title']) a;

The following result is returned:

{"glossary":{"GlossDiv":{"GlossList":{"GlossEntry":{"GlossTerm":"Standard Generalized Markup Language","GlossSee":"markup","SortAs":"SGML","GlossDef":{"para":"A meta-markup language, used to create markup languages such as DocBook.","GlossSeeAlso":["GML","XML"]},"ID":"SGML","Acronym":"SGML","Abbrev":"ISO 8879:1986"}}}}}

JSON_RESERVE

Syntax

json_reserve(json_string,json_path_string)
json_reserve(json_string,array[json_path_string])

Description

Returns a specified JSONPath expression string from a JSON string. You can use array[json_path_string] to specify multiple JSONPath expression strings at a time.

Return value type

VARCHAR (JSON string)

Examples

  select json_reserve(
  '{
      "glossary": {
          "title": "example glossary",
          "GlossDiv": {
              "title": "S",
              "GlossList": {
                  "GlossEntry": {
                      "ID": "SGML",
                      "SortAs": "SGML",
                      "GlossTerm": "Standard Generalized Markup Language",
                      "Acronym": "SGML",
                      "Abbrev": "ISO 8879:1986",
                      "GlossDef": {
                          "para": "A meta-markup language, used to create markup languages such as DocBook.",
                          "GlossSeeAlso": ["GML", "XML"]
                      },
                      "GlossSee": "markup"
                  }
              }
          }
      }
  }'
  , array['$.glossary.title']) a;

{"glossary":{"title":"example glossary"}} is returned.

  select json_reserve(
  '{
      "glossary": {
          "title": "example glossary",
          "GlossDiv": {
              "title": "S",
              "GlossList": {
                  "GlossEntry": {
                      "ID": "SGML",
                      "SortAs": "SGML",
                      "GlossTerm": "Standard Generalized Markup Language",
                      "Acronym": "SGML",
                      "Abbrev": "ISO 8879:1986",
                      "GlossDef": {
                          "para": "A meta-markup language, used to create markup languages such as DocBook.",
                          "GlossSeeAlso": ["GML", "XML"]
                      },
                      "GlossSee": "markup"
                  }
              }
          }
      }
  }'
  , array['$.glossary.title', '$.glossary.GlossDiv.title', '$.glossary.GlossDiv.GlossList.GlossEntry.ID']) a;

The following result is returned:

{"glossary":{"title":"example glossary","GlossDiv":{"GlossList":{"GlossEntry":{"ID":"SGML"}},"title":"S"}}}

JSON_EXTRACT_VALUR_COMPARE

Syntax

json_extract_value_compare(json, json_path, value_to_compare, operator)

Description

Compares the value extracted from a JSON string with the value specified by value_to_compare. If data types of the two values are inconsistent, this function automatically converts the value specified by value_to_compare to the data type of the value extracted from the JSON string before the comparison.

• json: the JSON string from which you want to extract a value.

• json_path: the JSONPath expression that you want to extract.

• value_to_compare: the string that you want to compare with the extracted value. Only strings are supported. If the value that is extracted is a numeric value, this function automatically converts the string specified by value_to_compare to a numeric value and then compares the two values.

• operator: the comparison operator. The following comparison operators are supported: <, >, ≤, ≥, =, !=, <>.

Return value type

BOOLEAN

Examples

select json_extract_value_compare('{"key":223}', '$.key', '9','gt');
+------------------------------------------------------------+
// 223 is a numeric value. You need to convert '9' to 9 and compare 9 with 223. 223 is greater than 9, and 1 is returned. 
|                                 1                          |
select json_extract_value_compare('{"key":"223"}', '$.key', '9', '>');
+------------------------------------------------------------+
// "223" is a string. You can directly compare "223" with "9". "223" is greater than "9", and 0 is returned. 
|                                 0                          |