Chapter 11. JSON (JavaScript Object Notation) Support

Index:

1. json and jsonb Operators
2. JSON Functions
3. JSON sample

json and jsonb Operators

JSON Functions

1. CAST(expression AS data_type): converts expression to another data type (json and jsonb). For instance, cast('["guia","test"]' as json), cast('33.56' as json), and cast('[{"a":"foo"},{"b":"bar"},{"c":"baz"}]' as jsonb).
   
2. CONVERT(expression, data_type): converts expression to another data type (json and jsonb). For instance, convert('["guia","test"]', json), convert('null',json), and convert('[{"a":"foo"},{"b":"bar"},{"c":"baz"}]' , json).
   
3. to_json(expression): Returns the value as json. Arrays and composites are converted (recursively) to arrays and objects; otherwise, if there is a cast from the type to json, the cast function will be used to perform the conversion; otherwise, a scalar value is produced. For any scalar type other than a number, a Boolean, or a null value, the text representation will be used, in such a fashion that it is a valid json or jsonb value.
4. to_jsonb(expression): Returns the value as jsonb.
5. jsonb_set(jsonObject, path, new_value[, create_missing_Flag]): Returns the value with the section designated by path replaced by new_value, or with new_value added if create_missing_Flag is true ( default is true) and the item designated by path does not exist.
6. json_extract(jsonObject, xpath, xpath[, xpath] ...): Extracts data from a JSON document.
7. JSON_TABLE(jsonContent[,xPath]): JSON_TABLE is a powerful function that enables the easy decomposition of JavaScript Object Notation (JSON) data into relational format. It can be quoted as a table in SQL.

JSON sample

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';