Postgresql – Extract JSON array of numbers from JSON array of objects

arrayjsonpostgresql

I have a table containing a json array of objects similar to this:

id    |   record
____________________
name1 | [{"a":0, "b":x}, {"a":1, "b":y}, {"a":2, "b":z}, ...]

I would like to get a table containing a json array of only, say, the "a" values:

id    |   record
____________________
name1 | [0, 1, 2, ...]

I use PostgreSQL 11, so the latest functions are acceptable.

Best Answer

You need to first unnest the array elements, and then aggregate back each value:

select id, 
       (select jsonb_agg(t -> 'a') from jsonb_array_elements(record) as x(t)) as record
from the_table;

Online example: https://rextester.com/ZONHTW97204

`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:

How to query a json column for empty objects?

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:

How to preserve the original order of elements in an unnested array?

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.

Postgresql – Postgres query to return JSON object keys as array

Sure, with json_object_keys(). This returns a set - unlike the JavaScript function Object.keys(obj) you are referring to, which returns an array. Feed the set to an ARRAY constructor to transform it:

SELECT id, ARRAY(SELECT json_object_keys(obj)) AS keys
FROM   tbl_items;

Or use jsonb_object_keys() for jsonb.

This returns an array of keys per row (not for the whole table).

A more verbose form would be to spell out a LATERAL join instead of the correlated subquery:

SELECT t.id, k.keys
FROM   tbl_items t
LEFT   JOIN LATERAL (SELECT ARRAY(SELECT * FROM json_object_keys(t.obj)) AS keys) k ON true;

Best Answer

Related Solutions

Postgresql – How to remove known elements from a JSON[] array in PostgreSQL

jsonb in Postgres 9.4 or later

json