Chapter 11. JSON (JavaScript Object Notation) Support
Index:
Operator | Right Operand Type | Description | Example | Example Result |
-> | int | Get JSON array element (indexed from zero, negative integers count from the end) | cast('[{"a":"foo"},{"b":"bar"},{"c":"baz"}]' as json)->2 | {"c":"baz"} |
-> | text | Get JSON object field by key | cast('{"a": {"b":"foo"}}' as json)->'a' | {"b":"foo"} |
->> | int | Get JSON array element as text | cast('[1,2,3]' as json)->>2 | 3 |
->> | text | Get JSON object field as text | cast('{"a":1,"b":2}' as json)->>'b' | 2 |
#> | text[] | Get JSON object at specified path | cast('{"a": {"b":{"c": "foo"}}}' as json)#>'{a,b}' | {"c": "foo"} |
#>> | text[] | Get JSON object at specified path as text | cast('{"a":[1,2,3],"b":[4,5,6]}' as json)#>>'{a,2}' | 3 |
select * from json_table ('{"employees": [{ "firstName":"John" , "lastName":"Doe" },{ "firstName":"Anna" , "lastName":"Smith" },{ "firstName":"Peter" , "lastName":"Jones" }]}','/employees') as atable; DECLARE v_json_obj json default '[{"a":"foo"},{"b":"bar"},{"c":"baz"}]'; select v_json_obj,v_json_obj->2; set v_json_obj=to_json(true); select v_json_obj; select cast('[{"a":"foo"},{"b":"bar"},{"c":"baz"}]' as json)->2 # output {"c":"baz"} select cast('[{"a":"foo"},{"b":"bar"},{"c":"baz"}]' as jsonb)->2 # maybe output {"c":"baz"} select cast('{"a": {"b":"foo"}, "c":{"a": "aaa"}}' as json)->'a' # output {"b":"foo"} select cast('[{"a":"foo"},{"b":"bar"},{"c":"baz"}]' as json)->>2 # output {"c":"baz"} select cast('{"a": {"b":"foo"}, "c":{"a": "aaa"}}' as json)->>'a' # output {"b":"foo"} select cast('{"a": {"b":{"c": "foo"}}}' as json)#> '{a,b}' # output {"c": "foo"} select cast('{"a": {"b":{"c": "foo"}}}' as json)#>> '{a,b}' # output {"c": "foo"} SELECT cast('{"bar": "baz", "balance": 7.77, "active":false}' as json); SELECT cast('{"bar": "baz", "balance": 7.77, "active":false}' as jsonb); SELECT convert('null',json); SELECT cast('33.56' as json); SELECT cast('"abcdef"' as json); SELECT convert('["guia","test"]', json); SELECT cast(null as json); select to_json('Fred said "Hi."'); select to_json(true); select to_jsonb(true); select jsonb_set('[{"f1":1,"f2":null},2]', '{0,f3}','[2,3,4]'); select jsonb_set('[{"f1":1,"f2":null},2,null,3]', '{0,f1}','[2,3,4]', false); create table if not exists testjson (jdoc jsonb); insert into testjson values('["guia","test"]');insert into testjson values('{"name": "Angel Back","company": "Tom Cat","is_active": true,"tags": ["gui","test"]}'); select * from testjson; select jdoc->'name' from testjson where jdoc->'company'='Tom Cat';