Types

The CAST syntax conforms to SQL; the syntax with :: is historical PostgreSQL usage. — Type Casts

jsonb

Extracting values Subscripting Since v11, Postgresql supports object_name['key'] and array_name[idx] syntax to extract values as an alternative to object_name->'key' and array_name->idx syntax. The result of a subscripting expression is always of the jsonb data type. Mapping between JSON and SQL SELECT value FROM jsonb_array_elements(arrayA) /** SELECT intersection_jsonb_arrays('[{"x": ["mark", 1, 1]}, {"x": ["mark", 1, 3]}]', '[{"x": ["mark", 1, 1]}, {"x": ["mark", 1, 3]}, {"x": ["mark", 2, 1]}, {"x": ["mark", 2, 3]}, {"x": ["mark", 3, 1]}, {"x": ["mark", 3, 2]}, {"x": ["mark", 3, 3]}]'); */ CREATE OR REPLACE FUNCTION intersection_jsonb_arrays(arrayA JSONB, arrayB JSONB) RETURNS JSONB LANGUAGE SQL AS $sql$ SELECT jsonb_agg(DISTINCT value) FROM ( SELECT value FROM jsonb_array_elements(arrayA) INTERSECT SELECT value FROM jsonb_array_elements(arrayB) ); $sql$; /** SELECT union_jsonb_arrays('[{"x": ["mark", 1, 1]}, {"x": ["mark", 1, 3]}]', '[{"x": ["mark", 1, 1]}, {"x": ["mark", 1, 3]}, {"x": ["mark", 2, 1]}, {"x": ["mark", 2, 3]}, {"x": ["mark", 3, 1]}, {"x": ["mark", 3, 2]}, {"x": ["mark", 3, 3]}]'); */ CREATE OR REPLACE FUNCTION union_jsonb_arrays(arrayA JSONB, arrayB JSONB) RETURNS JSONB LANGUAGE SQL AS $sql$ SELECT jsonb_agg(DISTINCT value) FROM ( SELECT value FROM jsonb_array_elements(arrayA) UNION SELECT value FROM jsonb_array_elements(arrayB) ); $sql$; SELECT key, value FROM jsonb_each(objectA)