7.16.15. json_extract#

Added in version 16.0.6.

Note

This is an experimental feature. Currently, this feature is still not stable.

7.16.15.1. Summary#

json_extract extracts values from a JSON by a JSONPath expression.

The extracted values keep their JSON types. For example, a string in JSON is extracted as a text and an integer in JSON is extracted as an integer. So you can use json_extract for both full text search against strings and range search against numbers.

json_extract is often used in combination with the filter option of select. For example, you can combine json_extract with between to do a range search against values that are stored in a JSON.

Note

json_extract requires the simdjson and jsoncons libraries. It isn’t available if Groonga is built without them.

7.16.15.2. Syntax#

json_extract requires two parameters:

json_extract(json, jsonpath)

7.16.15.3. Usage#

Here are a schema definition and sample data to show usage. The value column uses the JSON type and stores nested arrays:

Execution example:

table_create Data TABLE_NO_KEY
# [[0,1337566253.89858,0.000355720520019531],true]
column_create Data value COLUMN_SCALAR JSON
# [[0,1337566253.89858,0.000355720520019531],true]
load --table Data
[
{"value": "{\"value\": [[1, 10], [100]]}"},
{"value": "{\"value\": [[2], [20, 200]]}"},
{"value": "{\"value\": [[-1, -10], [-100]]}"}
]
# [[0,1337566253.89858,0.000355720520019531],3]

The following example extracts all elements of the nested arrays by the $.value[*][*] JSONPath and selects the records that have a value between 10 and 20:

Execution example:

select Data --filter 'between(json_extract(value, "$.value[*][*]"), 10, 20)'
# [
#   [
#     0,
#     1337566253.89858,
#     0.000355720520019531
#   ],
#   [
#     [
#       [
#         2
#       ],
#       [
#         [
#           "_id",
#           "UInt32"
#         ],
#         [
#           "value",
#           "JSON"
#         ]
#       ],
#       [
#         1,
#         {
#           "value": [
#             [
#               1,
#               10
#             ],
#             [
#               100
#             ]
#           ]
#         }
#       ],
#       [
#         2,
#         {
#           "value": [
#             [
#               2
#             ],
#             [
#               20,
#               200
#             ]
#           ]
#         }
#       ]
#     ]
#   ]
# ]

The records whose extracted values are between 10 and 20 are selected even though the values are stored in nested arrays in JSON.

7.16.15.4. Parameters#

There are two required parameters.

7.16.15.4.1. Required parameters#

7.16.15.4.1.1. json#

json is the target JSON. It must be a parsed JSON, such as a value of a JSON typed column, or a JSON string.

json_extract reports an error if this parameter isn’t a parsed JSON nor a JSON string.

7.16.15.4.1.2. jsonpath#

jsonpath is a JSONPath expression that matches the values to extract.

A JSONPath expression begins with $, which means the root of the JSON value. For example, $.value[*] matches all elements of the value array, and $.value[*][*] matches all elements of nested arrays under value.

json_extract reports an error if this parameter isn’t a valid JSONPath string.

7.16.15.5. Return value#

json_extract returns a vector that contains all values matched by the JSONPath expression. The extracted values keep their JSON types.

7.16.15.6. See also#