create schema test_json_extract;
set current_schema to 'test_json_extract';
select json_extract('{"a": "lihua"}', '$.a');
json_extract
--------------
"lihua"
(1 row)
select json_extract('{"a"}', '$.a');
ERROR: Invalid JSON text in argument 1 to function json_extract.
CONTEXT: referenced column: json_extract
select json_extract('{"a":[1, true, null]}', '$.a [1]');
json_extract
--------------
true
(1 row)
select json_extract('{"a":[1, true, null]}', '$.a [*]');
json_extract
-----------------
[1, true, null]
(1 row)
select json_extract('{"a":{"b": "hi"}}', '$ .* .b');
json_extract
--------------
["hi"]
(1 row)
select json_extract('{"a": 3233}', '$');
json_extract
--------------
{"a": 3233}
(1 row)
select json_extract('{"a": {"a": true}, "b": [{"a": "lihua"}]}', '$**.a');
json_extract
------------------------------
[{"a": true}, true, "lihua"]
(1 row)
select json_extract('{"a": "true", "b":{"a": 43}}', '$.a');
json_extract
--------------
"true"
(1 row)
select json_extract('{"a": "true", "b":{"a": 43}}', '$."a"');
json_extract
--------------
"true"
(1 row)
select json_extract(json_extract_path('{"a":{"b": [43, {"n": "hello"}]}}', 'a'), '$**.n');
json_extract
--------------
["hello"]
(1 row)
select json_extract(json_build_object('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}'), '$.a');
json_extract
--------------
1
(1 row)
select json_extract(json_build_array('a',1,'b',1.2,'c',true,'d',null,'e',json '{"x": 3, "y": [1,2,3]}'), '$[2]');
json_extract
--------------
"b"
(1 row)
select json_extract(cast('1' as json), '$[0]', '$[1]') as extra;
extra
-------
[1]
(1 row)
select json_extract(cast('"qweqwdas"' as json), '$[1]', '$[0]') as extra;
extra
--------------
["qweqwdas"]
(1 row)
select json_extract('["a", ["b", "c"], "d"]', '$[1]', '$[2]', '$[3]') as extra;
extra
-------------------
[["b", "c"], "d"]
(1 row)
select json_extract('["a", ["b", "c"], "d"]', '$.a', '$[2]', '$[3]') as extra;
extra
-------
["d"]
(1 row)
select json_extract('["a", ["b", "c"], {"d":"12.587"}]', '$[1][1]', '$[2]') as extra;
extra
------------------------
["c", {"d": "12.587"}]
(1 row)
select json_extract('["a", ["b", "c"], {"d":"1"}]', '$[2].d', '$[5]') as extra;
extra
-------
["1"]
(1 row)
select json_extract('{"a": 1, "b": 2, "c": {"d": 4}, "d":["e","x"]}', '$.a', '$.e');
json_extract
--------------
[1]
(1 row)
select json_extract('{"a": 1, "b": 2, "c": {"d": 4}, "d":["e","x"]}', '$.z', '$.e');
json_extract
--------------
(1 row)
select json_extract('{"a": 1, "b": 2, "c": {"d": 4}, "d":["e","x"]}', '$.a', '$.c.d', '$.d[1]');
json_extract
--------------
[1, 4, "x"]
(1 row)
select json_extract('{"a": 1, "b": 2, "c": {"d": 4}, "d":["e","x"]}', '$.a', '$.e');
json_extract
--------------
[1]
(1 row)
select json_extract('"az"', null);
json_extract
--------------
(1 row)
select json_extract('[1,"a"]', null);
json_extract
--------------
(1 row)
select json_extract('{"a":1}', null);
json_extract
--------------
(1 row)
select json_extract(cast('1' as json), null) as extra;
extra
-------
(1 row)
select json_extract(cast('"qweqwdas"' as json), NULL) as extra;
extra
-------
(1 row)
select json_extract('["a", ["b", "c"], {"d":"1"}]', null, '$[0]') as extra;
extra
-------
(1 row)
select json_extract('["a", ["b", "c"], {"d":"1"}]', NULL, '$[2]') as extra;
extra
-------
(1 row)
select json_extract(1, null);
ERROR: Invalid data type for JSON data in argument 1 to function json_extract
CONTEXT: referenced column: json_extract
select json_extract('{"a": 341522654875451.12345678901234567890123456789012345678901234567}', '$.a');
json_extract
-----------------------------------------------------------------
341522654875451.12345678901234567890123456789012345678901234567
(1 row)
create temp table test (
textjson json
);
insert into test values
('{"a": {"b": 32, "c":"hello"}, "d": 0.3443}'),
('["dog", "pig", {"a": "here"}]'),
('["pig", "dog", {"a": "there"}]'),
('{"a": "abc","b": {"b": "abc", "a": "abc"}}');
select json_extract(textjson, '$.*.c') from test;
json_extract
--------------
["hello"]
(4 rows)
select json_extract(textjson, '$**.a') from test;
json_extract
---------------------------
[{"b": 32, "c": "hello"}]
["here"]
["there"]
["abc", "abc"]
(4 rows)
insert into test values
(json_extract('{"a": 43, "b": {"c": true}}', '$.b'));
select * from test;
textjson
--------------------------------------------
{"a": {"b": 32, "c":"hello"}, "d": 0.3443}
["dog", "pig", {"a": "here"}]
["pig", "dog", {"a": "there"}]
{"a": "abc","b": {"b": "abc", "a": "abc"}}
{"c": true}
(5 rows)
select json_extract('{"id": "7", "name": 9223372036854775805}', '$.name');
json_extract
---------------------
9223372036854775805
(1 row)
select json_extract('{"id": "7", "name": 9223372036854775806}', '$.name');
json_extract
---------------------
9223372036854775806
(1 row)
select json_extract('{"id": "7", "name": 9223372036854775807}', '$.name');
json_extract
---------------------
9223372036854775807
(1 row)
select json_extract('{"id": "7", "name": 9223372036854776000}', '$.name');
json_extract
---------------------
9223372036854776000
(1 row)
select json_extract('{"id": "7", "name": 9223372036854776001}', '$.name');
json_extract
---------------------
9223372036854776001
(1 row)
select json_extract('{"id": "7", "name": 9.223372036854776e18}', '$.name');
json_extract
----------------------
9.223372036854776e18
(1 row)
select json_extract('{"id": "7", "name": 9223372036854775805}', '$.name') < json_extract('{"id": "7", "name": 9223372036854775806}', '$.name');
?column?
----------
t
(1 row)
select json_extract('{"id": "7", "name": 9223372036854775805}', '$.name') = json_extract('{"id": "7", "name": 9223372036854775806}', '$.name');
?column?
----------
f
(1 row)
drop schema test_json_extract cascade;
reset current_schema;
