PostgreSQL Json Functions and Operators

There are few functions and operators are available in PostgreSQL for JSON operations.

postgres=# create table jsontable(id int,j json, jb jsonb);
CREATE TABLE
postgres=# insert into jsontable values(1,'[{"a":"aaa"},{"b":"bbb"}]'::json,'[{"a":"aaa"},{"b":"bbb"}]'::jsonb);
INSERT 0 1
postgres=# select * from jsontable;
 id |             j             |              jb              
----+---------------------------+------------------------------
  1 | [{"a":"aaa"},{"b":"bbb"}] | [{"a": "aaa"}, {"b": "bbb"}]
(1 row)

postgres=# select '[{"a":"aaa"},{"b":"bbb"}]'::json;
           json            
---------------------------
 [{"a":"aaa"},{"b":"bbb"}]
(1 row)

postgres=# select '[{"a":"aaa"},{"b":"bbb"}]'::jsonb;
            jsonb             
------------------------------
 [{"a": "aaa"}, {"b": "bbb"}]
(1 row)

Json Operators

json -> integer → json
jsonb -> integer → jsonb
Extracts n’th element of JSON array
select ‘[{“a”:”foo”},{“b”:”bar”},{“c”:”baz”}]’::json -> 2; → {“c”:”baz”}
select ‘[{“a”:”foo”},{“b”:”bar”},{“c”:”baz”}]’::json -> -3; → {“c”:”foo”}
json -> text → json
jsonb -> text → jsonb
Extracts JSON object field with the given key.
select ‘{“a”: {“b”:”foo”}}’::json -> ‘a’; → {“b”:”foo”}
json ->> integer → text
jsonb ->> integer → text
Extracts n’th element of JSON array, as text.
select ‘[1,2,3]’::json ->> 2; → 3
json ->> text → text
jsonb ->> text → text
Extracts JSON object field with the given key, as text.
select ‘{“a”:1,”b”:2}’::json ->> ‘b’; → 2
json #> text[] → json
jsonb #> text[] → jsonb
Extracts JSON sub-object at the specified path, where path elements can be either field
keys or array indexes.
select ‘{“a”: {“b”: [“foo”,”bar”]}}’::json #> ‘{a,b,1}’; → “bar”
json #>> text[] → text
jsonb #>> text[] → text
Extracts JSON sub-object at the path as text.
select ‘{“a”: {“b”: [“foo”,”bar”]}}’::json #>> ‘{a,b,1}’; → bar
jsonb @> jsonb → boolean
Does the first JSON value contain the second?
select ‘{“a”:1, “b”:2}’::jsonb @>'{“b”:2}’::jsonb; → true
jsonb <@ jsonb → boolean
Is the first JSON value contained in the second?
select ‘{“b”:2}’::jsonb <@ ‘{“a”:1, “b”:2}’::jsonb; → true
jsonb ? text → boolean
Does the text string exist as a top-level key or array element within the JSON value?
select ‘{“a”:1, “b”:2}’::jsonb ? ‘b’; → true
select ‘[“a”, “b”, “c”]’::jsonb ? ‘b’;→ true
jsonb ?| text[] → boolean
Do any of the strings in the text array exist as top-level keys or array elements?
select ‘{“a”:1, “b”:2, “c”:3}’::jsonb ?| array[‘b’, ‘d’]; → true
jsonb ?& text[] → boolean
Do all of the strings in the text array exist as top-level keys or array elements?
select ‘[“a”, “b”, “c”]’::jsonb ?& array[‘a’, ‘b’]; → true
jsonb || jsonb → jsonb
Concatenates two jsonb values
select ‘[“a”, “b”]’::jsonb || ‘[“a”, “d”]’::jsonb; → [“a”, “b”, “a”,”d”]
select ‘[1, 2]’::jsonb || ‘3’::jsonb; → [1, 2, 3]
select ‘{“a”: “b”}’::jsonb || ’42’::jsonb; → [{“a”: “b”}, 42]
jsonb text → jsonb
Deletes a key (and its value) from a JSON object, or matching string value(s) from a
JSON array
select ‘{“a”: “b”, “c”: “d”}’::jsonb – ‘a’; → {“c”:”d”}
select ‘[“a”, “b”, “c”, “b”]’::jsonb – ‘b’;
[“a”, “c”]
jsonb – text[] → jsonb
Deletes all matching keys or array elements from the left operand
select ‘{“a”: “b”, “c”: “d”}’::jsonb – ‘{a,c}’::text[]; → {}
jsonb – integer → jsonb
Deletes the array element with specified index (negative integers count from the end).
Throws an error if JSON value is not an array
select ‘[“a”, “b”]’::jsonb – 1; → [“a”]
jsonb #- text[] → jsonb
Deletes the field or array element at the specified path, where path elements can be either
field keys or array indexes.
select ‘[“a”, {“b”:1}]’::jsonb #- ‘{1,b}’;
→ [“a”, {}]
jsonb @? jsonpath → boolean
Does JSON path return any item for the specified JSON value?
select ‘{“a”:[1,2,3,4,5]}’::jsonb @? ‘$.a[*] ? (@ > 2)’; → true
jsonb @@ jsonpath → boolean
Returns the result of a JSON path predicate check for the specified JSON value. Only the
first item of the result is taken into account. If the result is not Boolean, then NULL is returned
select ‘{“a”:[1,2,3,4,5]}’::jsonb @@ ‘$.a[*] > 2’ ; → true

Json Functions

to_json ( anyelement ) → json
to_jsonb ( anyelement ) → jsonb
select to_json(‘Google said “Bye.”‘::text); → “Google said \”Bye.\””
select to_jsonb(row(42, ‘Google said “Bye.”‘::text));
{“f1”: 42,
“f2”: “Google said \”Bye.\””}
array_to_json ( anyarray [, boolean ] ) → json
Converts an SQL array to a JSON array. The behavior is the same as to_json
select array_to_json(‘{{1,2},{10,20}}’::int[]); → [[1,2],[10,20]]
row_to_json ( record [, boolean ] ) → json
Converts an SQL composite value to a JSON object
select row_to_json(row(1,’abc’)); → {“f1″:1,”f2″:”abc”}
json_build_array ( VARIADIC “any” ) → json
jsonb_build_array ( VARIADIC “any” ) → jsonb
Builds a possibly-heterogeneously-typed JSON array out of a variadic argument list.
select json_build_array(1, 2, ‘abc’, 4, 5); → “[1, 2, “”abc””, 4, 5]”
json_build_object ( VARIADIC “any” ) → json
jsonb_build_object ( VARIADIC “any” ) → jsonb
Builds a JSON object out of a variadic argument list
select json_build_object(‘abc’, 1, 2, row(3,’xyz’)); → {“abc” : 1, “2” : {“f1″:3,”f2″:”xyz”}}
json_object ( text[] ) → json
jsonb_object ( text[] ) → jsonb
Builds a JSON object out of a text array
select json_object(‘{a, 1, b, “def”, c, 3.5}’); → {“a” : “1”, “b” : “def”, “c” : “3.5”}
json_object ( keys text[], values text[] ) → json
jsonb_object( keys text[], values text[] ) →jsonb
This form of json_object takes keys and values pairwise from separate text arrays.
select json_object(‘{a,b}’, ‘{1,2}’);
→ {“a”: “1”, “b”: “2”}
json_array_elements ( json ) → setof json
jsonb_array_elements ( jsonb ) → setof jsonb
Expands the top-level JSON array into a set of JSON values
select * from json_array_elements(‘[1,true, [2,false]]’);
1
true
[2,false]
json_each ( json ) → setof record ( key text, value json )
jsonb_each ( jsonb ) → setof record ( key text, value jsonb )
Expands the top-level JSON object into a set of key/value pairs
select * from json_each(‘{“a”:”abc”, “b”:”xyz”}’);
key | value
—–+——-
a | “abc”
b | “xyz”
json_each_text ( json ) → setof record ( key text, value text )
jsonb_each_text ( jsonb ) → setof record ( key text, value text )
Expands the top-level JSON object into a set of key/value pairs. The returned values
will be of type text
select * from json_each_text(‘{“a”:”foo”, “b”:”bar”}’);
key | value
—–+——-
a | abc
b | xyz
json_extract_path ( from_json json, VARIADIC path_elems text[] ) → json
jsonb_extract_path ( from_json jsonb, VARIADIC path_elems text[] ) →
jsonb
Extracts JSON sub-object at the specified path
select json_extract_path(‘{“f2”:{“f3″:1},”f4”:{“f5″:99,”f6″:”abc”}}’, ‘f4’, ‘f6’); → abc
json_extract_path_text ( from_json json, VARIADIC path_elems text[] )
→ text
jsonb_extract_path_text ( from_json jsonb, VARIADIC path_elems text[]
) → text
Extracts JSON sub-object at the specified path as text
select json_extract_path_text(‘{“f2”:{“f3″:1},”f4”:
{“f5″:99,”f6″:”abc”}}’, ‘f4’, ‘f6’);
→ abc
json_object_keys ( json ) → setof text
jsonb_object_keys ( jsonb ) → setof text
Returns the set of keys in the top-level JSON object.
select * fromjson_object_keys(‘{“f1″:”abc”,”f2″:{“f3″:”a”,”f4″:”b”}}’);
json_object_keys
f1
f2
json_populate_record ( base anyelement, from_json json ) → anyelement
jsonb_populate_record ( base anyelement, from_json jsonb ) → anyelement
Expands the top-level JSON object to a row having the composite type of the base argument.
Note:- A JSON null value is converted to an SQL null in all cases
create type subrowtype as (d int, e text);
create type myrowtype as (a int, b text[], c subrowtype);
select * from json_populate_record(null::myrowtype, ‘{“a”:1, “b”: [“2”, “a b”], “c”: {“d”: 4, “e”: “a b c”},”x”:”foo”}’);


a | b | c
—+———–+————-
1 | {2,”a b”} | (4,”a b c”)
jsonb_populate_recordset ( base anyelement, from_json jsonb ) → setof
anyelement
Expands the top-level JSON array of objects to a set of rows having the composite type
of the base argument
create type twoints as (a int, b int);
select * from json_populate_recordset(null::twoints,
‘[{“a”:1,”b”:2}, {“a”:3,”b”:4}]’);

a | b
—+—
1 | 2
3 | 4
json_to_record ( json ) → record
jsonb_to_record ( jsonb ) → record
Expands the top-level JSON object to a row having the composite type defined by an AS clause
create type myrowtype as (a int, b text);
select * from json_to_record(‘{“a”:1,”b”:[1,2,3],”c”:
[1,2,3],”e”:”bar”,”r”: {“a”: 123, “b”: “a b c”}}’) as x(a
int, b text, c int[], d text, r myrowtype);

a | b | c | d | r
—+———+———+—+—————
1 | [1,2,3] | {1,2,3} | | (123,”a b c”)
json_to_recordset ( json ) → setof record
jsonb_to_recordset ( jsonb ) → setof record
Expands the top-level JSON array of objects to a set of rows having the composite type defined by an AS clause
select * from json_to_recordset(‘[{“a”:1,”b”:”foo”},
{“a”:”2″,”c”:”bar”}]’) as x(a int, b text);

a | b
—+—–
1 | foo
2 |
jsonb_set ( target jsonb, path text[], new_value jsonb [, create_if_missing
boolean ] ) → jsonb
Returns target with the item designated by path replaced by new_value
select jsonb_set(‘[{“f1″:1,”f2”:null},2,null,3]’, ‘{0,f1}’,
‘[2,3,4]’, false);

[{“f1”: [2, 3, 4], “f2”: null}, 2, null,3]
jsonb_insert ( target jsonb, path text[], new_value jsonb [, insert_after
boolean ] ) → jsonb
Returns target with new_value inserted
select jsonb_insert(‘{“a”: [0,1,2]}’, ‘{a, 1}’, ‘”new_value”‘); → {“a”: [0, “new_value”, 1, 2]}
json_strip_nulls ( json ) → json
jsonb_strip_nulls ( jsonb ) → jsonb
Deletes all object fields that have null values from the given JSON value, recursively.
Null values that are not object fields are untouched
select json_strip_nulls(‘[{“f1”:1, “f2”:null}, 2, null, 3]’);
[{“f1”:1},2,null,3]
jsonb_path_exists ( target jsonb, path jsonpath [, vars jsonb [, silent
boolean ]] ) → boolean
Checks whether the JSON path returns any item for the specified JSON value
select jsonb_path_exists(‘{“a”:[1,2,3,4,5]}’, ‘$.a[*] ? (@ >= $min && @ <= $max)’, ‘{“min”:2, “max”:4}’); → true
jsonb_path_match ( target jsonb, path jsonpath [, vars jsonb [, silent
boolean ]] ) → boolean
Returns the result of a JSON path predicate check for the specified JSON value
select jsonb_path_match(‘{“a”:[1,2,3,4,5]}’, ‘exists($.a[*] ? (@ = $min && @ <= $max))’, ‘{“min”:2, “max”:4}’); → true
jsonb_path_query ( target jsonb, path jsonpath [, vars jsonb [, silent
boolean ]] ) → setof jsonb
Returns all JSON items returned by the JSON path for the specified JSON value
select * from jsonb_path_query(‘{“a”:[1,2,3,4,5]}’,
‘$.a[*] ? (@ >= $min && @ <= $max)’, ‘{“min”:2, “max”:4}’);


jsonb_path_query
2
3
4
jsonb_path_query_array ( target jsonb, path jsonpath [, vars jsonb [,
silent boolean ]] ) → jsonb
Returns all JSON items returned by the JSON path for the specified JSON value
select jsonb_path_query_array(‘{“a”:[1,2,3,4,5]}’, ‘$.a[*] ? (@ >= $min && @ <= $max)’, ‘{“min”:2, “max”:4}’); → [2, 3, 4]
jsonb_path_query_first ( target jsonb, path jsonpath [, vars jsonb [,
silent boolean ]] ) → jsonb
Returns the first JSON item returned by the JSON path for the specified JSON value
select jsonb_path_query_first(‘{“a”:[1,2,3,4,5]}’, ‘$.a[*] ? (@ >= $min && @ <= $max)’, ‘{“min”:2, “max”:4}’); → 2
jsonb_pretty ( jsonb ) → text
Converts the given JSON value to pretty-printed, indented text.
select jsonb_pretty(‘[{“f1″:1,”f2”:null}, 2]’);
jsonb_pretty
[ +
{ +
“f1”: 1, +
“f2”: null+
}, +
2 +
]
json_typeof ( json ) → text
jsonb_typeof ( jsonb ) → text
Returns the type of the top-level JSON value as a text string
select json_typeof(‘-123.4’); → number
select json_typeof(‘null’::json); → null
select json_typeof(NULL::json) IS NULL; → true

Nowadays Json data type data are used extensively across web applications, web sites etc. So these function may come handy.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: