postgres: find nested json value that matches value in array

Question

I have a product table, where data is a jsonb field. The JSON inside looks like this:

{
  id: 1,
  name: "Paper",
  locations: [
     {locationId: 111, x: 1, y:1},
     {locationId: 210, x: 27, y:86},
     {locationId: 140, x: 34, y:134},
  ]
}

I have a query that returns the product where at least one location id matches a given id.

SELECT id FROM product WHERE product.data ->'locations' @> '[{"locationId: GIVEN_ID}]'

Now here's the question:I want to adapt the query for a GIVEN_ID_LIST to that I get all product where at least one locationId is in GIVEN_ID_LIST.

S-Man · Accepted Answer · 2020-11-26 15:39:30Z

step-by-step demo:db<>fiddle

   SELECT DISTINCT                                                   -- 4
        mycol ->> 'name'                                             -- 3
    FROM
        mytable,
        json_array_elements(mycol -> 'locations') as elements        -- 1
    WHERE '[111, 210]'::jsonb @> (elements -> 'locationId')::jsonb   -- 2

Extract the array elements
Check if array element is part of your list (which is converted to type jsonb for being able to apply the @> operator)
Get product name
Because you could match several ids in one product, you have to eliminate these duplicates.

user330315 · Accepted Answer · 2020-11-26 15:28:53Z

0

For an OR condition, you need to unnest the array and combine that with an EXISTS condition

select p.id
from product p
where exists (select *
              from jsonb_array_elements(p.data -> 'locations') as x(item)
              where x.item ->> 'locationId' in ('1', '2', '3'));

answered Nov 26, 2020 at 15:28

user330315

Collectives™ on Stack Overflow

postgres: find nested json value that matches value in array

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related