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 except_jsonb_arrays('["red", "blue", "teal", "green", "yellow", "magenta"]', '["green"]');
*/

CREATE OR REPLACE FUNCTION except_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)
  EXCEPT
  SELECT value FROM jsonb_array_elements(arrayB)
);
$sql$;```



### `SELECT key, value FROM jsonb_each(objectA)`