All Products
Search
Document Center

JSON functions and operators

Last Updated: Aug 08, 2019

JSON_ARRAY_CONTAINS

  1. json_array_contains(json,value)
  • Command description: determines whether value exists in the JSON array.

  • Return value type: BOOLEAN

  • Examples:

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

JSON_ARRAY_GET

  1. 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:

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

JSON_ARRAY_LENGTH

  1. json_array_length(json)
  • Command description: returns the length of the json array.

  • Return value type: BIGINT

  • Examples:

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

JSON _EXTRACT

  1. 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:

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

JSON_EXTRACT_SCALAR

  1. 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:

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

JSON_ARRAY_EXTRACT/JSON_ARRAY_EXTRACT_SCALAR

  1. json_array_extract(json,jsonPath)
  • Command description: returns the json_path from json.

  • Return value type: VARCHAR (array string)

  • Examples:

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

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

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

    Returned result: [12, 14]

JSON_FORMAT

  1. json_format(json)
  • Command description: returns the JSON text serialized from the input json.

  • Return value type: VARCHAR

  • Examples:

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

JSON_PARSE

  1. json_parse(string)
  • Command description: returns the JSON value deserialized from the input string.

  • Return value type: JSON

  • Examples:

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

JSON_SIZE

  1. json_size(json,json_path)
  • Command description: returns the size of json.

  • Return value type: BIGINT

  • Examples:

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

JSON_REMOVE

  1. json_remove(json_string,json_path_string)
  2. 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:

    1. select json_remove(
    2. '{
    3. "glossary": {
    4. "title": "example glossary",
    5. "GlossDiv": {
    6. "title": "S",
    7. "GlossList": {
    8. "GlossEntry": {
    9. "ID": "SGML",
    10. "SortAs": "SGML",
    11. "GlossTerm": "Standard Generalized Markup Language",
    12. "Acronym": "SGML",
    13. "Abbrev": "ISO 8879:1986",
    14. "GlossDef": {
    15. "para": "A meta-markup language, used to create markup languages such as DocBook.",
    16. "GlossSeeAlso": ["GML", "XML"]
    17. },
    18. "GlossSee": "markup"
    19. }
    20. }
    21. }
    22. }
    23. }'
    24. , '$.glossary.GlossDiv') a;

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

    1. select json_remove(
    2. '{
    3. "glossary": {
    4. "title": "example glossary",
    5. "GlossDiv": {
    6. "title": "S",
    7. "GlossList": {
    8. "GlossEntry": {
    9. "ID": "SGML",
    10. "SortAs": "SGML",
    11. "GlossTerm": "Standard Generalized Markup Language",
    12. "Acronym": "SGML",
    13. "Abbrev": "ISO 8879:1986",
    14. "GlossDef": {
    15. "para": "A meta-markup language, used to create markup languages such as DocBook.",
    16. "GlossSeeAlso": ["GML", "XML"]
    17. },
    18. "GlossSee": "markup"
    19. }
    20. }
    21. }
    22. }
    23. }'
    24. , 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”}}}}}

  1. ## JSON_RESERVE
  2. ```sql
  3. json_reserve(json_string,json_path_string)
  4. 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:

    1. select json_reserve(
    2. '{
    3. "glossary": {
    4. "title": "example glossary",
    5. "GlossDiv": {
    6. "title": "S",
    7. "GlossList": {
    8. "GlossEntry": {
    9. "ID": "SGML",
    10. "SortAs": "SGML",
    11. "GlossTerm": "Standard Generalized Markup Language",
    12. "Acronym": "SGML",
    13. "Abbrev": "ISO 8879:1986",
    14. "GlossDef": {
    15. "para": "A meta-markup language, used to create markup languages such as DocBook.",
    16. "GlossSeeAlso": ["GML", "XML"]
    17. },
    18. "GlossSee": "markup"
    19. }
    20. }
    21. }
    22. }
    23. }'
    24. , array['$.glossary.title']) a;

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

    1. select json_reserve(
    2. '{
    3. "glossary": {
    4. "title": "example glossary",
    5. "GlossDiv": {
    6. "title": "S",
    7. "GlossList": {
    8. "GlossEntry": {
    9. "ID": "SGML",
    10. "SortAs": "SGML",
    11. "GlossTerm": "Standard Generalized Markup Language",
    12. "Acronym": "SGML",
    13. "Abbrev": "ISO 8879:1986",
    14. "GlossDef": {
    15. "para": "A meta-markup language, used to create markup languages such as DocBook.",
    16. "GlossSeeAlso": ["GML", "XML"]
    17. },
    18. "GlossSee": "markup"
    19. }
    20. }
    21. }
    22. }
    23. }'
    24. , 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”}}}```