Types
The CAST syntax conforms to SQL; the syntax with :: is historical PostgreSQL usage. — Type Casts
- Numeric
- Monetary
- Character
- Binary
- Date/Time
- Boolean
- Enumerated
- Geometric
- Network Address
- Bit String
- Text Search
- UUID
- XML
- JSON
- Arrays
- Composite
- Range
- Domain
- Object Identifier
- pg_lsn
- Pseudo
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)`