JSON functions and operators - SQL Reference| Alibaba Cloud Documentation Center

JSON_ARRAY_CONTAINS

json_array_contains(json,value)

Command description: determines whether value exists in the JSON array.
Return value type: BOOLEAN

Examples:

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

JSON_ARRAY_GET

json_array_get(json_array,index)

Command description: returns the element at the specified index into json_array.
- index >= 0: returns the specified elements from the start of json_array.
- index < 0: returns the specified elements from the end of json_array.
- No specified element at index: returns NULL.
Return value type: VARCHAR

Examples:

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

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

JSON_ARRAY_LENGTH

json_array_length(json)

Command description: returns the length of the json array.
Return value type: BIGINT

Examples:

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

JSON _EXTRACT

json_extract(json,json_path)

Command description: returns the json_path from json, in $[n] format.
- If n is less than 0, the system reports an error.
- If n is an invalid parameter whose value is greater than 0, the returned result is NULL.
Return value type: JSON

Examples:

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

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

JSON_EXTRACT_SCALAR

json_extract_scalar(json,json_path)

Command description: returns the json_path from json, in $[n] format.
- If n is less than 0, the system reports an error.
- If n is an invalid parameter whose value is greater than 0, the returned result is NULL.
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

json_array_extract(json,jsonPath)

Command description: returns the json_path from json.
Return value type: VARCHAR (array string)

Examples:

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

Returned result: ["12", "14"]

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

Returned result: [12, 14]

JSON_FORMAT

json_format(json)

Command description: returns the JSON text serialized from the input json.
Return value type: VARCHAR

Examples:

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

JSON_PARSE

json_parse(string)

Command description: returns the JSON value deserialized from the input string.
Return value type: JSON

Examples:

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

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

JSON_SIZE

json_size(json,json_path)

Command description: returns the size of json.
Return value type: BIGINT

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

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

Command description: returns data from json_string, excluding json_path_string.

You can use array[json_path_string] to specify multiple values of json_path_string at a time.
Return value type: VARCHAR (JSON-formatted 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;

Returned result: {"glossary":{"title":"example glossary"}}

 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;

Returned result:

```sql

{“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
```sql
json_reserve(json_string,json_path_string)
json_reserve(json_string,array[json_path_string])

Command description: returns json_path_string from json_string.

You can use array[json_path_string]to specify multiple values ofjson_path_string.
Return value type: VARCHAR (JSON-formatted 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;

Returned result:{"glossary":{"title":"example glossary"}}

 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;

Returned result:

```sql

{“glossary”:{“title”:”example glossary”,”GlossDiv”:{“GlossList”:{“GlossEntry”:{“ID”:”SGML”}},”title”:”S”}}}```