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