Select Unique value from a JSON Array - PostgreSQL JSON column

Question

I have the following JSON document stored in a PostgreSQL JSON column:

{
  "status": "Success",
  "message": "",
  "data": {
    "serverIp": "XXXX",
    "ruleId": 32321,
    "results": [
      {
        "versionId": 555555,
        "PriceID": "8abf35ec-3e0e-466b-a4e5-2af568e90eec",
        "price": 350,
        "Convert": 0.8,
        "Cost": 15
        "Customer_ID":1
      },
      {
        "versionId": 4444,
        "PriceID": "b5a1dbd5-17b4-4847-8b3c-da334f95276a",
        "price": 550,
        "Convert": 0.7,
        "Cost": 10,
        "Customer_ID":10
      }
    ]
  }
}

I am trying to retrieve the price for specific customer_ID I am using this query to get the price for Customer_ID=1

select json_array_elements(t.info -> 'data' -> 'results') ->> 'price'
from mytable  t
where exists (
    select 
    from json_array_elements(t.info -> 'data' -> 'results') x(elt)
    where (x.elt ->> 'Customer_ID')::int = 1
)

The problem is that i am getting the same results for Customer_ID=1 and for Customer_ID=10 I am basically getting both elements of the array instead of just one. I don't know what i am doing wrong

Answer 1

You can use a lateral join to unnest the array elements, and then filter on the customer with a where clause; this leaves you with just one row, from which you can extract the price:

select x.elt ->> 'price' price
from mytable t 
cross join lateral json_array_elements(t.info -> 'data' -> 'results') x(elt)
where x.elt ->> 'Customer_ID' = 1

Note that you don't need to cast the customer id to an int, as it is already stored in the proper format in the json array.