hstore_to_jsonb.rst

Operators

• Jsonb elements

Operator	Returns	Description	Example	Result
jsonb - text	jsonb	delete key from left operand	'{"a":1, "b":2, "c": 3}'::jsonb -'b'::text	{"a":1, "c": 3}
jsonb - integer	jsonb	delete index from left operand	'{[2, 3, 8]}' '::jsonb - 1	{[2, 8]}
jsonb - text[]	jsonb	delete keys from left operand	'{"a":1, "b":2, "c": 3}'::jsonb - ARRAY ['a','b']	{"c":3}
jsonb - jsonb	jsonb	delete matching pairs from left operand	'{"a":1, "b":2, "c": 3}'::jsob - '{"a":4, "b":2}'::jsonb	{"a":1, "c": 3}
jsonb #- text[]	jsonb	delete key path from left operand	'{"a": {"b": { "c": [1,2]}}}}'::jsonb # - '{a,b,c,0}'	{"a": {"b": {"c": [2]}}}

• Convertion

Operator	Returns	Description	Example	Result
%% jsonb	text[]	convert jsonb to array of alternating keys and values	%% '{"a":foo, "b": bar}'::jsonb	{a,foo,b,bar}
%# jsonb	text[]	convert jsonb to two-dimensional key/value array	%# '{"a":foo, "b": bar}'::jsonb	{{a,foo}, {b,bar}}

• Concatenation

Operator	Returns	Description	Example	Result
jsonb || jsonb	jsonb	concatenate jsonb objects	'{"a":b, "c": d}'::jsonb || '{"c": x, "d":q}'::jsonb	{"a":b, "c":x "d":q}

• Other

Operator	Returns	Description
record #= jsonb	record	replace fields in record with matching values from jsonb(*)

(*) example of usage:

CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');

SELECT (r).* FROM (SELECT t #= '"col3"=>"baz"' AS r FROM test t) s;

col1	col2	col3
123	foo	baz

Functions

• Jsonb elements

Function	Return Type	Description	Example	Result
defined(jsonb,text) !!! wrong - jsonb_exists !!! implements this	boolean	does jsonb contain non-NULL value for key?	defined('{"a": NULL}','a')	f
replace(jsonb,text[],jsonb)	jsonb	replace value at the specified path	replace('{"a":1,"b": {"c":3,"d":[4,5,6]}}' '::jsonb,'{b,d}', '1')	{"a":1, "b": {"c":3, "d" :1}}
delete(jsonb,text)	jsonb	delete pair with matching key	delete('{"a":1,"b":2}' ,'b')	{"a":1}
delete(jsonb,text[])	jsonb	delete pairs with matching keys	delete('{"a":1,"b":2,"c" :3}',ARRAY['a','b'])	{"c":3}
delete(jsonb,jsonb)	jsonb	delete pairs matching those in the second argument	delete('{"a":1,"b":2} ,'{"a":4,"b":2}'::jsonb	{"a":1}
jsonb_move_path(jsonb,text[], text[])	jsonb	move value at the specific path to another path	move('{"a":1,"b":{"c":3, ,"d":[4,5,6]}}' '::jsonb,'{b,d}', '{a, f}')	{"a":1, "b": {"c":3}, "f":[4,5,6]}
jsonb_copy_path(jsonb,text[], text[])	jsonb	copy value at the specific path to another path	copy('{"a":1,"b":{"c":3, ,"d"=>[4,5,6]}}' '::jsonb,'{b,d}', '{f}')	{"a":1, "b": {"c":3, "d": [4,5,6]}, "f":[4,5,6]}

• Concatenation

Function	Return Type	Description	Example	Result
concat_path(jsonb,text[],jsonb)	jsonb	concatenate jsonb value at the specified path	concat_path('{"b":{"c": 3,"d":[4,5,6]}}' '::jsonb,'{b,d}', '1')	{"b":{"c":3, "d":[4, 5, 6, 1]}}

• Convertion

Function	Return Type	Description	Example	Result
jsonb_to_array(jsonb)	text[]	get jsonb keys and values as an array of alternating keys and values	jsonb_to_array('{"a":1, 1,"b":2}')	{a,1,b,2}
jsonb_to_matrix(jsonb)	text[]	get jsonb keys and values as a two-dimensional array	jsonb_to_matrix('{"a": 1,"b":2}')	{{a,1},{b,2}}
array_to_jsonb(anyarray)	jsonb	construct an array jsonb from an array	array_to_jsonb(' {{1,1,4},{23,3,5}} '::int[])	{[[1, 1, 4], [23, 3, 5]]}
slice(jsonb, text[])	jsonb	extract a subset of an jsonb	slice('{"a":1,"b":2, "c":3}'::jsonb, ARRAY ['b','c','x'])	{"b":2, "c": 3}
row_to_jsonb(record [,pretty_bool])	jsonb	return the row as jsonb	row_to_jsonb(row(1, 'foo'))	{"f1":1, "f2":"foo"}
jsonb_agg(record)	jsonb	aggregate record values and return jsonb(*)

(*) example of usage:

CREATE TABLE test (col1 integer, col2 text, col3 text);
INSERT INTO test VALUES (123, 'foo', 'bar');

SELECT jsonb_agg(test) FROM test;

        jsonb_agg
----------------------------------
[{"col1": 123, "col2": 'foo', "col3": 'bar'}]

• Other

Function	Return Type	Description	Example	Result
akeys(jsonb)	text[]	get jsonb keys as an array	akeys('{"a":1,"b":2}')	{a,b}
skeys(jsonb)	setof text	get jsonb keys as a set	skeys('{"a":1,"b":2}')	a b
avals(jsonb)	text[]	get jsonb values as an array	avals('{"a":1,"b":2}')	{1,2}
svals(jsonb)	setof text	get jsonb values as a set	svals('{"a":1,"b":2}')	1 2
jvals(jsonb)	setof jsonb	get jsonb values as a set of jsonb objects	jvals('{"a":[1,2], "b":{foo=>1}}')	[1, 2] "foo"=>1
each_jsonb(jsonb)	setof (key text, value text)	get jsonb keys and values as a set	select * from + each_jsonb('{"a":1, "b":2}') +	key | value -----+--------+ a | 1 -----+--------+ b | 2
jsonb_print (jsonb,bool,bool,bool,bool,bool)	text	Format an jsonb value as text with various formatting options options options	jsonb_print('{"a":t, "t":f,"arr":[1,2,3] }', pretty_print := true)	"a": t, + "t": f, + "arr": + [ + 1, + 2, + 3 + ]