-- json_get functions --
SELECT json_get_int(parse_json('{"a": {"b": {"c": 1}}}'), 'a.b.c');

+------------------------------------------------------------------------+
| json_get_int(parse_json(Utf8("{"a": {"b": {"c": 1}}}")),Utf8("a.b.c")) |
+------------------------------------------------------------------------+
| 1                                                                      |
+------------------------------------------------------------------------+

SELECT json_get_float(parse_json('{"a": {"b": {"c": 1.234}}}'), 'a:b.c');

+------------------------------------------------------------------------------+
| json_get_float(parse_json(Utf8("{"a": {"b": {"c": 1.234}}}")),Utf8("a:b.c")) |
+------------------------------------------------------------------------------+
| 1.234                                                                        |
+------------------------------------------------------------------------------+

SELECT json_get_string(parse_json('{"a": {"b": {"c": "foo"}}}'), 'a.b:c');

+-------------------------------------------------------------------------------+
| json_get_string(parse_json(Utf8("{"a": {"b": {"c": "foo"}}}")),Utf8("a.b:c")) |
+-------------------------------------------------------------------------------+
| foo                                                                           |
+-------------------------------------------------------------------------------+

SELECT json_get_bool(parse_json('{"a": {"b": {"c": true}}}'), 'a.b["c"]');

+-------------------------------------------------------------------------------+
| json_get_bool(parse_json(Utf8("{"a": {"b": {"c": true}}}")),Utf8("a.b["c"]")) |
+-------------------------------------------------------------------------------+
| true                                                                          |
+-------------------------------------------------------------------------------+

SELECT json_get_int(parse_json('{"a": {"b": {"c": {"d": 1}}}}'), 'a.b');

+-----------------------------------------------------------------------------+
| json_get_int(parse_json(Utf8("{"a": {"b": {"c": {"d": 1}}}}")),Utf8("a.b")) |
+-----------------------------------------------------------------------------+
|                                                                             |
+-----------------------------------------------------------------------------+

SELECT json_get_string(parse_json('{"a": {"b": {"c": {"d": 1}}}}'), 'a.b');

+--------------------------------------------------------------------------------+
| json_get_string(parse_json(Utf8("{"a": {"b": {"c": {"d": 1}}}}")),Utf8("a.b")) |
+--------------------------------------------------------------------------------+
|                                                                                |
+--------------------------------------------------------------------------------+

-- test functions with table rows --
CREATE TABLE jsons(j JSON, ts timestamp time index);

Affected Rows: 0

INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": 1}}}'), 1);

Affected Rows: 1

INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": 1.234}}}'), 2);

Affected Rows: 1

INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": "foo"}}}'), 3);

Affected Rows: 1

INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": true}}}'), 4);

Affected Rows: 1

SELECT json_get_int(j, 'a.b.c') FROM jsons;

+-------------------------------------+
| json_get_int(jsons.j,Utf8("a.b.c")) |
+-------------------------------------+
| 1                                   |
|                                     |
|                                     |
| 1                                   |
+-------------------------------------+

SELECT json_get_float(j, 'a["b"].c') FROM jsons;

+------------------------------------------+
| json_get_float(jsons.j,Utf8("a["b"].c")) |
+------------------------------------------+
| 1.0                                      |
| 1.234                                    |
|                                          |
| 1.0                                      |
+------------------------------------------+

SELECT json_get_string(j, 'a.b.c?(@ == 1)') FROM jsons;

+-------------------------------------------------+
| json_get_string(jsons.j,Utf8("a.b.c?(@ == 1)")) |
+-------------------------------------------------+
| 1                                               |
|                                                 |
|                                                 |
|                                                 |
+-------------------------------------------------+

SELECT json_get_bool(j, 'a.b.c') FROM jsons;

+--------------------------------------+
| json_get_bool(jsons.j,Utf8("a.b.c")) |
+--------------------------------------+
|                                      |
|                                      |
|                                      |
| true                                 |
+--------------------------------------+

SELECT json_get_int(j, 'a.b["c"]') FROM jsons;

+----------------------------------------+
| json_get_int(jsons.j,Utf8("a.b["c"]")) |
+----------------------------------------+
| 1                                      |
|                                        |
|                                        |
| 1                                      |
+----------------------------------------+

DROP TABLE jsons;

Affected Rows: 0

-- test functions with arrays --
CREATE TABLE jsons(j JSON, ts timestamp time index);

Affected Rows: 0

INSERT INTO jsons VALUES(parse_json('["a", "bcde", "", "Long time ago, there is a little pig flying in the sky"]'), 1);

Affected Rows: 1

INSERT INTO jsons VALUES(parse_json('[true, false, false, false]'), 2);

Affected Rows: 1

INSERT INTO jsons VALUES(parse_json('[1, 0, -2147483649, 2147483648]'), 3);

Affected Rows: 1

INSERT INTO jsons VALUES(parse_json('[1.2, 3.1415926535897932384626, -3e123, 1e100]'), 4);

Affected Rows: 1

SELECT json_get_int(j, '[0]') FROM jsons;

+-----------------------------------+
| json_get_int(jsons.j,Utf8("[0]")) |
+-----------------------------------+
|                                   |
| 1                                 |
| 1                                 |
|                                   |
+-----------------------------------+

SELECT json_get_float(j, '[1]') FROM jsons;

+-------------------------------------+
| json_get_float(jsons.j,Utf8("[1]")) |
+-------------------------------------+
|                                     |
| 0.0                                 |
| 0.0                                 |
| 3.141592653589793                   |
+-------------------------------------+

SELECT json_get_bool(j, '[2]') FROM jsons;

+------------------------------------+
| json_get_bool(jsons.j,Utf8("[2]")) |
+------------------------------------+
|                                    |
| false                              |
|                                    |
|                                    |
+------------------------------------+

SELECT json_get_string(j, '[3]') FROM jsons;

+--------------------------------------------------------+
| json_get_string(jsons.j,Utf8("[3]"))                   |
+--------------------------------------------------------+
| Long time ago, there is a little pig flying in the sky |
| false                                                  |
| 2147483648                                             |
| 1e100                                                  |
+--------------------------------------------------------+

DROP TABLE jsons;

Affected Rows: 0

-- test functions in WHERE clause --
CREATE TABLE jsons(j JSON, ts timestamp time index);

Affected Rows: 0

INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": 1}}}'), 1);

Affected Rows: 1

INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": 1.234}}}'), 2);

Affected Rows: 1

INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": "foo"}}}'), 3);

Affected Rows: 1

INSERT INTO jsons VALUES(parse_json('{"a": {"b": {"c": true}}}'), 4);

Affected Rows: 1

SELECT json_to_string(j) FROM jsons WHERE json_get_int(j, 'a.b.c') = 1;

+-------------------------+
| json_to_string(jsons.j) |
+-------------------------+
| {"a":{"b":{"c":1}}}     |
| {"a":{"b":{"c":true}}}  |
+-------------------------+

SELECT json_to_string(j) FROM jsons WHERE json_get_float(j, 'a.b.c') = 1.234;

+-------------------------+
| json_to_string(jsons.j) |
+-------------------------+
| {"a":{"b":{"c":1.234}}} |
+-------------------------+

SELECT json_to_string(j) FROM jsons WHERE json_get_string(j, 'a.b.c') = 'foo';

+-------------------------+
| json_to_string(jsons.j) |
+-------------------------+
| {"a":{"b":{"c":"foo"}}} |
+-------------------------+

SELECT json_to_string(j) FROM jsons WHERE json_get_bool(j, 'a.b.c') = true;

+-------------------------+
| json_to_string(jsons.j) |
+-------------------------+
| {"a":{"b":{"c":true}}}  |
+-------------------------+

SELECT json_to_string(j) FROM jsons WHERE CAST(json_get_int(j, 'a.b.c') AS BOOLEAN);

+-------------------------+
| json_to_string(jsons.j) |
+-------------------------+
| {"a":{"b":{"c":1}}}     |
| {"a":{"b":{"c":true}}}  |
+-------------------------+

DROP TABLE jsons;

Affected Rows: 0