Chapter 11. JSON (JavaScript Object Notation) Support


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

json and jsonb Operators

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

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

Copyright © 2003-2019 Heng Xing Tian Tai Lab | All Rights Reserved. |