CODEWITHSUNDEEP
sqljsonpostgresql
Vitalii Zurian
Vitalii Zurian

Reputation: 17976

How to filter json array returned from a JSON column in a PostgreSQL table?

I am trying to get the reduced (filtered?) JSON array from a table

Consider the following example

CREATE TABLE "public"."test_data" (
    "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
    "a_field" jsonb NOT NULL,
    PRIMARY KEY ("id")
);

INSERT INTO test_data (a_field) VALUES 
('[ {"id": 1, "value": 100}, {"id": 2, "value": 200}, {"id": 2, "value": 50} ]');

The data structure stored in the test_data.a_field field is an array of { id: number, value: number } objects.

Given the example above, I need to get the data for a specific id of the object, so instead of this

[ {"id": 1, "value": 100}, {"id": 2, "value": 200}, {"id": 2, "value": 50} ]

I could get this?

[ {"id": 2, "value": 200}, {"id": 2, "value": 50} ]

Please help.

Upvotes: 0

Views: 703

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65323

You can use jsonb_array_elements() and jsonb_agg() functions consecutively :

SELECT jsonb_agg(js) as a_field_new
  FROM
  (
   SELECT jsonb_array_elements(a_field) as js
     FROM test_data
  ) q
 WHERE js->> 'id' = '2'

OR without subquery :

SELECT jsonb_agg(value) as a_field_new 
  FROM test_data
 CROSS JOIN jsonb_array_elements(a_field)
 WHERE value->> 'id' = '2'

Demo

Upvotes: 2

Related Questions