set client_min_messages to 'error';
drop extension if exists "json_accessors" cascade;
create extension "json_accessors";
set client_min_messages to 'notice';
\t on
\pset format unaligned
-- qq
select json_get_text('{"foo":"qq", "bar": true}', 'foo');
qq
-- t
select json_get_boolean('{"foo":"qq", "bar": true}', 'bar');
t
-- 42
select json_get_int('{"baz": 42, "boo": 42.424242}', 'baz');
42
-- 42
select json_get_bigint('{"baz": 42, "boo": 42.424242}', 'baz');
42
-- 9223372036854
select json_get_bigint('{"baz": 9223372036854, "boo": 42.424242}', 'baz');
9223372036854
-- 42.424242
select json_get_numeric('{"baz": 42, "boo": 42.424242}', 'boo');
42.424242
-- t
select json_get_text('{"foo":"qq", "bar": true}', 'noneofthese') is null;
t
-- t
select json_get_text('{"foo":null, "bar": true}', 'foo') is null;
t
-- Tue Dec 01 01:23:45 2009
select json_get_timestamp('{"foo":"qq", "bar": "2009-12-01 01:23:45"}', 'bar');
Tue Dec 01 01:23:45 2009
set time zone EST;
-- 2009-12-01 01:23:45
select to_char(json_get_timestamp('{"foo":"qq", "bar": "2009-12-01 01:23:45"}', 'bar'), 'YYYY-MM-DD HH:MI:SSTZ');
2009-12-01 01:23:45
set time zone "Europe/Moscow";
-- 2009-12-01 01:23:45
select to_char(json_get_timestamp('{"foo":"qq", "bar": "2009-12-01 01:23:45"}', 'bar'), 'YYYY-MM-DD HH:MI:SSTZ');
2009-12-01 01:23:45
-- {foo,bar}
select json_array_to_text_array('["foo", "bar"]');
{foo,bar}
-- {t,f}
select json_array_to_boolean_array('[true, false]');
{t,f}
-- {42,43}
select json_array_to_int_array('[42, 43]');
{42,43}
-- {42,9223372036854}
select json_array_to_bigint_array('[42, 9223372036854]');
{42,9223372036854}
-- {42.4242,43.4343}
select json_array_to_numeric_array('[42.4242,43.4343]');
{42.4242,43.4343}
-- {"Tue Dec 01 01:23:45 2009","Sat Dec 01 01:23:45 2012"}
select json_array_to_timestamp_array('["2009-12-01 01:23:45", "2012-12-01 01:23:45"]');
{"Tue Dec 01 01:23:45 2009","Sat Dec 01 01:23:45 2012"}
-- {baz1,baz2,baz3}
select json_get_text_array('{"foo":"qq", "bar": ["baz1", "baz2", "baz3"]}', 'bar');
{baz1,baz2,baz3}
-- {t,f}
select json_get_boolean_array('{"foo":"qq", "bar": [true, false]}', 'bar');
{t,f}
-- {42,43}
select json_get_int_array('{"foo":"qq", "bar": [42, 43]}', 'bar');
{42,43}
-- {42,9223372036854}
select json_get_bigint_array('{"foo":"qq", "bar": [42, 9223372036854]}', 'bar');
{42,9223372036854}
-- {42.4242,43.4343}
select json_get_numeric_array('{"foo":"qq", "bar": [42.4242,43.4343]}', 'bar');
{42.4242,43.4343}
-- {"Tue Dec 01 01:23:45 2009","Sat Dec 01 01:23:45 2012"}
select json_get_timestamp_array('{"foo":"qq", "bar": ["2009-12-01 01:23:45", "2012-12-01 01:23:45"]}', 'bar');
{"Tue Dec 01 01:23:45 2009","Sat Dec 01 01:23:45 2012"}
-- {baz1,NULL,baz3}
select json_get_text_array('{"foo":"qq", "bar": ["baz1", null, "baz3"]}', 'bar');
{baz1,NULL,baz3}
-- {NULL,"Sat Dec 01 01:23:45 2012"}
select json_get_timestamp_array('{"foo":"qq", "bar": [null, "2012-12-01 01:23:45"]}', 'bar');
{NULL,"Sat Dec 01 01:23:45 2012"}
-- "qq"
select json_get_object('{"foo":"qq", "bar": ["baz1", "baz2", "baz3"]}', 'foo');
"qq"
-- ["baz1","baz2","baz3"]
select json_get_object('{"foo":"qq", "bar": ["baz1", "baz2", "baz3"]}', 'bar');
["baz1","baz2","baz3"]
-- {"baz1":"baz2"}
select json_get_object('{"foo":"qq", "bar": {"baz1": "baz2"}}', 'bar');
{"baz1":"baz2"}
-- {baz1,baz2,baz3}
select json_array_to_text_array(json_get_object('{"foo":"qq", "bar": ["baz1", "baz2", "baz3"]}', 'bar'));
{baz1,baz2,baz3}
-- baz2
select (json_array_to_text_array(json_get_object('{"foo":"qq", "bar": ["baz1", "baz2", "baz3"]}', 'bar')))[2];
baz2
-- {"{\"foo\":42}","{\"bar\":[]}"}
select json_array_to_object_array('[{"foo":42}, {"bar":[]}]');
{"{\"foo\":42}","{\"bar\":[]}"}
-- {}
select json_array_to_object_array('[]');
{}
-- {"{\"foo\":42}","{\"bar\":[]}"}
select json_get_object_array('{"key" : [{"foo":42}, {"bar":[]}]}', 'key');
{"{\"foo\":42}","{\"bar\":[]}"}
\t off
\pset format aligned