json_extract

description

Syntax

  1. `VARCHAR json_extract(VARCHAR json_str, VARCHAR path[, VARCHAR path] ...))`
  2. JSON jsonb_extract(JSON j, VARCHAR json_path)
  3. BOOLEAN json_extract_isnull(JSON j, VARCHAR json_path)
  4. BOOLEAN json_extract_bool(JSON j, VARCHAR json_path)
  5. INT json_extract_int(JSON j, VARCHAR json_path)
  6. BIGINT json_extract_bigint(JSON j, VARCHAR json_path)
  7. LARGEINT json_extract_largeint(JSON j, VARCHAR json_path)
  8. DOUBLE json_extract_double(JSON j, VARCHAR json_path)
  9. STRING json_extract_string(JSON j, VARCHAR json_path)

json_extract functions extract field specified by json_path from JSON. A series of functions are provided for different datatype.

  • json_extract with VARCHAR argument, extract and return VARCHAR datatype
  • jsonb_extract extract and return JSON datatype
  • json_extract_isnull check if the field is json null and return BOOLEAN datatype
  • json_extract_bool extract and return BOOLEAN datatype
  • json_extract_int extract and return INT datatype
  • json_extract_bigint extract and return BIGINT datatype
  • json_extract_largeint extract and return LARGEINT datatype
  • json_extract_double extract and return DOUBLE datatype
  • json_extract_STRING extract and return STRING datatype

json path syntax:

  • ‘$’ for json document root
  • ‘.k1’ for element of json object with key ‘k1’
    • If the key column value contains “.”, double quotes are required in json_path, For example: SELECT json_extract(‘{“k1.a”:”abc”,”k2”:300}’, ‘$.”k1.a”‘);
  • ‘[i]‘ for element of json array at index i
    • Use ‘$[last]‘ to get the last element of json_array, and ‘$[last-1]‘ to get the penultimate element, and so on.

Exception handling is as follows:

  • if the field specified by json_path does not exist, return NULL
  • if datatype of the field specified by json_path is not the same with type of json_extract_t, return t if it can be cast to t else NULL

json_exists_path and json_type

description

Syntax

  1. BOOLEAN json_exists_path(JSON j, VARCHAR json_path)
  2. STRING json_type(JSON j, VARCHAR json_path)

There are two extra functions to check field existence and type

  • json_exists_path check the existence of the field specified by json_path, return TRUE or FALS
  • json_type get the type as follows of the field specified by json_path, return NULL if it does not exist
    • object
    • array
    • null
    • bool
    • int
    • bigint
    • largeint
    • double
    • string

example

refer to json tutorial for more.

  1. mysql> SELECT json_extract('{"id": 123, "name": "doris"}', '$.id');
  2. +------------------------------------------------------+
  3. | json_extract('{"id": 123, "name": "doris"}', '$.id') |
  4. +------------------------------------------------------+
  5. | 123                                                  |
  6. +------------------------------------------------------+
  7. 1 row in set (0.01 sec)
  9. mysql> SELECT json_extract('[1, 2, 3]', '$.[1]');
  10. +------------------------------------+
  11. | json_extract('[1, 2, 3]', '$.[1]') |
  12. +------------------------------------+
  13. | 2                                  |
  14. +------------------------------------+
  15. 1 row in set (0.01 sec)
  17. mysql> SELECT json_extract('{"k1": "v1", "k2": { "k21": 6.6, "k22": [1, 2] } }', '$.k1', '$.k2.k21', '$.k2.k22', '$.k2.k22[1]');
  18. +-------------------------------------------------------------------------------------------------------------------+
  19. | json_extract('{"k1": "v1", "k2": { "k21": 6.6, "k22": [1, 2] } }', '$.k1', '$.k2.k21', '$.k2.k22', '$.k2.k22[1]') |
  20. +-------------------------------------------------------------------------------------------------------------------+
  21. | ["v1",6.6,[1,2],2]                                                                                                |
  22. +-------------------------------------------------------------------------------------------------------------------+
  23. 1 row in set (0.01 sec)
  25. mysql> SELECT json_extract('{"id": 123, "name": "doris"}', '$.aaa', '$.name');
  26. +-----------------------------------------------------------------+
  27. | json_extract('{"id": 123, "name": "doris"}', '$.aaa', '$.name') |
  28. +-----------------------------------------------------------------+
  29. | [null,"doris"]                                                  |
  30. +-----------------------------------------------------------------+
  31. 1 row in set (0.01 sec)

keywords

JSONB, JSON, json_extract, json_extract_isnull, json_extract_bool, json_extract_int, json_extract_bigint, json_extract_largeint,json_extract_double, json_extract_string, json_exists_path, json_type