json_query¶

Signature

json_query(json_string, query)

Description

Extract data from json_string using advanced json querying offered by JMESPath.

JMESPath Links:

See JMESPath Tutorial for exhaustive query possibilities

GitHub of jmespath.js

Examples

1. Basic Query

EUUSeurope-west1

select bigfunctions.eu.json_query('{"foo": [{"first": "a"}, {"first": "c"}]}', 'foo')

select bigfunctions.us.json_query('{"foo": [{"first": "a"}, {"first": "c"}]}', 'foo')

select bigfunctions.europe_west1.json_query('{"foo": [{"first": "a"}, {"first": "c"}]}', 'foo')

+----------------------------------+
| result                           |
+----------------------------------+
| [{"first": "a"}, {"first": "c"}] |
+----------------------------------+

2. Getting array sub-items

EUUSeurope-west1

select bigfunctions.eu.json_query('{"foo": [{"first": "a"}, {"first": "c"}]}', 'foo[*].first')

select bigfunctions.us.json_query('{"foo": [{"first": "a"}, {"first": "c"}]}', 'foo[*].first')

select bigfunctions.europe_west1.json_query('{"foo": [{"first": "a"}, {"first": "c"}]}', 'foo[*].first')

+------------+
| result     |
+------------+
| ['a', 'c'] |
+------------+

3. Slicing

EUUSeurope-west1

select bigfunctions.eu.json_query('{"foo": [{"first": "a"}, {"first": "c"}]}', 'foo[:1].first')

select bigfunctions.us.json_query('{"foo": [{"first": "a"}, {"first": "c"}]}', 'foo[:1].first')

select bigfunctions.europe_west1.json_query('{"foo": [{"first": "a"}, {"first": "c"}]}', 'foo[:1].first')

+--------+
| result |
+--------+
| ['a']  |
+--------+

4. Projecting

EUUSeurope-west1

select bigfunctions.eu.json_query('{"foo": [{"first": "a"}, {"first": "c"}]}', 'foo[*].{name: first}')

select bigfunctions.us.json_query('{"foo": [{"first": "a"}, {"first": "c"}]}', 'foo[*].{name: first}')

select bigfunctions.europe_west1.json_query('{"foo": [{"first": "a"}, {"first": "c"}]}', 'foo[*].{name: first}')

+--------------------------------+
| result                         |
+--------------------------------+
| [{"name": "a"}, {"name": "c"}] |
+--------------------------------+