i have this table :
And i want display all the names in the table like this :
Also i want to list the number of gender each genre : Desired output:
PostgreSQL – How to Unnest and Display Elements of a JSON Array
jsonpostgresql
Related Solutions
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.
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 1You 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;
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;
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.
I have this solution for MySQL 5.7, where you have to do the work manually. In the case of MySQL 8.0+ you can simply use JSON_TABLE
SELECT
JSON_EXTRACT(C.AddressIdentifiers, CONCAT('$[', Numbers.N - 1, '].Identifier')) AS Identifier,
JSON_EXTRACT(C.AddressIdentifiers, CONCAT('$[', Numbers.N - 1, '].AddressType')) AS AddressType,
FROM
(
SELECT @row := @row + 1 AS N FROM
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T2,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) T1,
(SELECT @row:=0) T0
) Numbers -- Natural numbers from 1 to 100
INNER JOIN Customers C ON Numbers.N < JSON_LENGTH(C.AddressIdentifiers)
Best Answer
According to the documentation, the
#>operator comes in useful:SqlFiddle