Select from JSON Array postgresql JSON column

Question

I have the following JSON stored in a PostgreSQL JSON column

{
  "status": "Success",
  "message": "",
  "data": {
    "serverIp": "XXXX",
    "ruleId": 32321,
    "results": [
      {
        "versionId": 555555,
        "PriceID": "8abf35ec-3e0e-466b-a4e5-2af568e90eec",
        "price": 550,
        "Convert": 0.8922953080331764,
        "Cost": 10
      }
    ]
  }
}

I would like to search for a specific priceID across the entire JSON column (name info) and select the entire results element by the PriceID. How do i do that in postgresql JSON?

GMB · Accepted Answer · 2020-05-03 23:04:53Z

3

One option uses exists and json(b)_array_elements(). Assuming that your table is called mytable and that the jsonb column is mycol, this would look like:

select t.*
from mytable t
where exists (
    select 1
    from jsonb_array_elements(t.mycol -> 'data' -> 'results') x(elt)
    where x.elt ->> 'PriceID' = '8abf35ec-3e0e-466b-a4e5-2af568e90eec'
)

In the subquery, jsonb_array_elements() unnest the json array located at the given path. Then, the where clause ensures that at least one elment in the array has the given PriceID.

If your data is of json datatype rather than jsonb, you need to use json_array_elements() instead of jsonb_array_elements().

If you want to display some information coming from the matching array element, then it is different. You can use a lateral join instead of exists. Keep in mind, though, that this will duplicate the rows if more than one array element matches:

select t.*, x.elt ->> 'price' price
from mytable t
cross join lateral jsonb_array_elements(t.mycol -> 'data' -> 'results') x(elt)
where x.elt ->> 'PriceID' = '8abf35ec-3e0e-466b-a4e5-2af568e90eec'

edited May 3, 2020 at 23:04

answered May 3, 2020 at 0:05

GMB

224k25 gold badges103 silver badges151 bronze badges

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

4 Comments

Ofer B Over a year ago

This is great! thank you so much You don't know how many different queries i tried How can i just get a specific element within the results array like price and also i can i get the entire results array? Thanks again

Ofer B Over a year ago

Thank you for your reply, it worked great but when i try the same thing with an integer value i get an error message:ERROR: operator does not exist: text = integer select t.* from mytable t where exists ( select 1 from jsonb_array_elements(t.mycol -> 'data' -> 'results') x(elt) where x.elt ->> 'cost' = 800 )

GMB Over a year ago

@OferB: you need to cast to the proper datatye, so either (x.elt ->> 'cost')::int = 800 or x.elt ->> 'cost' = 800::text

Ofer B Over a year ago

tried it like this: select json_array_elements(t.info -> 'data' -> 'results') ->> 'Cost' from table t where exists ( select from json_array_elements(t.info -> 'data' -> 'results') x(elt) where (x.elt ->> 'ID')::int = 10 ) The result i got is where ID=10 and ID=1 I just need the 10 , what did i do wrong?

Collectives™ on Stack Overflow

Select from JSON Array postgresql JSON column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related