SQLANDPLSQL.COM

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.