CODEWITHSUNDEEP
sqlarraysjsonpostgresqlselect
Ofer B
Ofer B

Reputation: 429

Select from JSON Array postgresql JSON column

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?

Upvotes: 1

Views: 1681

Answers (1)

GMB
GMB

Reputation: 222652

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'

Upvotes: 3

Related Questions