Select matching element from jsonb in Postgres

Question

I have the the table orders which contains a column called stores with a jsonb with many elements like

 id  status        stores
  1  in progress   [{"id": 1, "lat": 19.41, "lng": -99.18, "name": "Domino´s pizza condesa", "products": "3 tacos de cabeza"},
                    {"id": 2, "lat": 19.03, "lng": -99.9, "name": "Papa guapa roma", "products": "una papa"}]

  1  done          [{"id": 3, "lat": 19.44, "lng": -99.28, "name": "ABC", "products": "3 tacos de cabeza"},
                    {"id": 4, "lat": 19.23, "lng": -99.29, "name": "Papa guapa roma", "products": "una papa"}]

I want to query the table orders and select only the element from the json that matches {"lat:19.41", "lng":-99.18}

So I get something like

id  status        store_filtered
 1  in progress   [{"id": 1, "lat": 19.41, "lng": -99.18, "name": "Domino´s pizza condesa", "products": "3 tacos de cabeza"}]

I currently have

SELECT id, status, stores->0 AS stores_filtered FROM orders WHERE stores @> '[{"lat":19.41, "lng": -99.18}]'

But that 0 is what I need to make dynamic, to give me the element that matches not the first.

Jesus Talamantes Cortes · Accepted Answer · 2017-03-03 22:50:12Z

0

with r as ( select status, unnest(stores) as stores from orders ) select * from r where stores->lat='19.41' and stores->lng='-99.18'

edited Mar 3, 2017 at 22:50

answered Mar 3, 2017 at 22:42

Jesus Talamantes Cortes

11 bronze badge

Sign up to request clarification or add additional context in comments.

2 Comments

Jesus Talamantes Cortes Over a year ago

with r as ( select status, unnest(stores) as stores from orders ) select * from r where stores->lat='19.41' and stores->lng='-99.18'

koceeng Over a year ago

Please add more description and/or information about your answer and how it solves the asked problem so others can easily understand it without asking for clarification

joanolo · Accepted Answer · 2017-04-02 11:18:23Z

Asssuming that you're using PostgreSQL 9.5 (or newer), you can use the jsonb_array_elements(json) (or jsonb_array_elements(json)) functions to "unnest" the array, and then choose.

For instance, you can try:

WITH orders(id, status, stores) AS
(
VALUES
    (1,  'in progress',
        '[{"id": 1, "lat": 19.41, "lng": -99.18, "name": "Domino´s pizza condesa", "products": "3 tacos de cabeza"},
         {"id": 2, "lat": 19.03, "lng": -99.9, "name": "Papa guapa roma", "products": "una papa"}]'::jsonb),

    (1,  'done', 
         '[{"id": 3, "lat": 19.44, "lng": -99.28, "name": "ABC", "products": "3 tacos de cabeza"},
           {"id": 4, "lat": 19.23, "lng": -99.29, "name": "Papa guapa roma", "products": "una papa"}]')
)
, expanded_stores AS
(
SELECT
    id, status, jsonb_array_elements(stores) AS single_store
FROM
    orders
)
SELECT
    id, status, single_store AS filtered_store
FROM
    expanded_stores
WHERE 
    single_store @> '{"lat":19.41, "lng": -99.18}'

Or, assuming that you already have declared your orders table, you could just use:

SELECT
    id, status, single_store AS filtered_store
FROM
    (
    SELECT
        id, status, jsonb_array_elements(stores) AS single_store
    FROM
        orders
    ) AS expanded_stores
WHERE 
    single_store @> '{"lat":19.41, "lng": -99.18}' ;

You can check version 9.5 at http://rextester.com/MQE36487.

NOTE: Although this does work, it looks a bit "unnatural" for a SQL database. It is normally easier to normalize the information, and reserve JSON just where a lot of schema flexibility is really needed.

Collectives™ on Stack Overflow

Select matching element from jsonb in Postgres

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related