Postgres 9.4 or newer
Obviously inspired by this post, Postgres 9.4 added the missing function(s):
Thanks to Laurence Rowe for the patch and Andrew Dunstan for committing!
To unnest the JSON array. Then use array_agg()
or an ARRAY constructor to build a Postgres array from it. Or string_agg()
to build a text
string.
Aggregate unnested elements per row in a LATERAL
or correlated subquery. Then original order is preserved and we don't need ORDER BY
, GROUP BY
or even a unique key in the outer query. See:
Replace 'json' with 'jsonb' for jsonb
in all following SQL code.
SELECT t.tbl_id, d.list
FROM tbl t
CROSS JOIN LATERAL (
SELECT string_agg(d.elem::text, ', ') AS list
FROM json_array_elements_text(t.data->'tags') AS d(elem)
) d;
Short syntax:
SELECT t.tbl_id, d.list
FROM tbl t, LATERAL (
SELECT string_agg(value::text, ', ') AS list
FROM json_array_elements_text(t.data->'tags') -- col name default: "value"
) d;
Related:
ARRAY constructor in correlated subquery:
SELECT tbl_id, ARRAY(SELECT json_array_elements_text(t.data->'tags')) AS txt_arr
FROM tbl t;
Related:
Subtle difference: null
elements are preserved in actual arrays. This is not possible in the above queries producing a text
string, which cannot contain null
values. The true representation is an array.
Function wrapper
For repeated use, to make this even simpler, encapsulate the logic in a function:
CREATE OR REPLACE FUNCTION json_arr2text_arr(_js json)
RETURNS text[] LANGUAGE sql IMMUTABLE PARALLEL SAFE AS
'SELECT ARRAY(SELECT json_array_elements_text(_js))';
Make it an SQL function, so it can be inlined in bigger queries.
Make it IMMUTABLE
(because it is) to avoid repeated evaluation in bigger queries and allow it in index expressions.
Make it PARALLEL SAFE
(in Postgres 9.6 or later!) to not stand in the way of parallelism. See:
Call:
SELECT tbl_id, json_arr2text_arr(data->'tags')
FROM tbl;
db<>fiddle here
Postgres 9.3 or older
Use the function json_array_elements()
. But we get double quoted strings from it.
Alternative query with aggregation in the outer query. CROSS JOIN
removes rows with missing or empty arrays. May also be useful for processing elements. We need a unique key to aggregate:
SELECT t.tbl_id, string_agg(d.elem::text, ', ') AS list
FROM tbl t
CROSS JOIN LATERAL json_array_elements(t.data->'tags') AS d(elem)
GROUP BY t.tbl_id;
ARRAY constructor, still with quoted strings:
SELECT tbl_id, ARRAY(SELECT json_array_elements(t.data->'tags')) AS quoted_txt_arr
FROM tbl t;
Note that null
is converted to the text value "null", unlike above. Incorrect, strictly speaking, and potentially ambiguous.
Poor man's unquoting with trim()
:
SELECT t.tbl_id, string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM tbl t, json_array_elements(t.data->'tags') d(elem)
GROUP BY 1;
Retrieve a single row from tbl:
SELECT string_agg(trim(d.elem::text, '"'), ', ') AS list
FROM tbl t, json_array_elements(t.data->'tags') d(elem)
WHERE t.tbl_id = 1;
Strings form correlated subquery:
SELECT tbl_id, (SELECT string_agg(trim(value::text, '"'), ', ')
FROM json_array_elements(t.data->'tags')) AS list
FROM tbl t;
ARRAY constructor:
SELECT tbl_id, ARRAY(SELECT trim(value::text, '"')
FROM json_array_elements(t.data->'tags')) AS txt_arr
FROM tbl t;
Original (outdated) SQL Fiddle.
db<>fiddle here.
Related:
Notes (outdated since pg 9.4)
We would need a json_array_elements_text(json)
, the twin of json_array_elements(json)
to return proper text
values from a JSON array. But that seems to be missing from the provided arsenal of JSON functions. Or some other function to extract a text
value from a scalar JSON
value. I seem to be missing that one, too.
So I improvised with trim()
, but that will fail for non-trivial cases ...
jsonb
in Postgres 9.4 or later
Consider the jsonb
data type in Postgres 9.4 or later. The 'b' at the end stands for 'binary'. Among other things, there is an equality operator (=
) for jsonb
. Most people will want to switch.
Depesz blog about jsonb.
json
There is no =
operator defined for the data type json
, because there is no well defined method to establish equality for whole json
values. But see below.
You could cast to text
and then use the =
operator. This is short, but only works if your text representation happens to match. Inherently unreliable, except for corner cases. See:
Or you can unnest
the array and use the ->>
operator to .. get JSON object field as text
and compare individual fields.
Test table
2 rows: first one like in the question, second one with simple values.
CREATE TABLE tbl (
tbl_id int PRIMARY KEY
, jar json[]
);
INSERT INTO t VALUES
(1, '{"{\"value\" : \"03334/254146\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f5\"}"
,"{\"value\" : \"03334/254147\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f6\"}"
,"{\"value\" : \"03334/254148\", \"typeId\" : \"ea4e7d7e-7b87-4628-ba50-f7\"}"}')
, (2, '{"{\"value\" : \"a\", \"typeId\" : \"x\"}"
,"{\"value\" : \"b\", \"typeId\" : \"y\"}"
,"{\"value\" : \"c\", \"typeId\" : \"z\"}"}');
Demos
Demo 1
You could use array_remove()
with text
representations (unreliable).
SELECT tbl_id
, jar, array_length(jar, 1) AS jar_len
, jar::text[] AS t, array_length(jar::text[], 1) AS t_len
, array_remove(jar::text[], '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-f6"}'::text) AS t_result
, array_remove(jar::text[], '{"value" : "03334/254147", "typeId" : "ea4e7d7e-7b87-4628-ba50-f6"}'::text)::json[] AS j_result
FROM tbl;
Demo 2
Unnest the array and test fields of individual elements.
SELECT tbl_id, array_agg(j) AS j_new
FROM tbl, unnest(jar) AS j -- LATERAL JOIN
WHERE j->>'value' <> '03334/254146'
AND j->>'typeId' <> 'ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf5'
GROUP BY 1;
Demo 3
Alternative test with row type.
SELECT tbl_id, array_agg(j) AS j_new
FROM tbl, unnest(jar) AS j -- LATERAL JOIN
WHERE (j->>'value', j->>'typeId') NOT IN (
('03334/254146', 'ea4e7d7e-7b87-4628-ba50-6a5f6e63dbf5')
,('a', 'x')
)
GROUP BY 1;
UPDATE
as requested
Finally, this is how you could implement your UPDATE
:
UPDATE tbl t
SET jar = j.jar
FROM tbl t1
CROSS JOIN LATERAL (
SELECT ARRAY(
SELECT j
FROM unnest(t1.jar) AS j -- LATERAL JOIN
WHERE j->>'value' <> 'a'
AND j->>'typeId' <> 'x'
) AS jar
) j
WHERE t1.tbl_id = 2 -- only relevant rows
AND t1.tbl_id = t.tbl_id;
db<>fiddle here
About the implicit LATERAL JOIN
:
About unnesting arrays:
DB design
To simplify your situation consider an normalized schema: a separate table for the json
values (instead of the array column), joined in a n:1 relationship to the main table.
Best Answer
The post you linked to was mine. That was on an array, but the operator is the same for an object (
||
) concatentation.Append a new item like
{'d' : 4}
Update the key
'c'
with the value 5Upsert the already existing key
'd'
with the value 6End result,