json_array_elements returns empty dataset in postgres

2 votes

1 answer

376 views

                          I was trying to read elements from Json array inside another array that is stored on Postgres database, but my result set was always empty.
My table looks like below:

Sample Json from table:

    {"test":{"accountName":"OS1U","details":[{"status":{"statusCode":"Test Ready","modifiedDate":"2023-07-31T14:53:49Z"},"products":[{"productId":"0001","productName":"2GB","productDescription":"testproduct12"},{"productId":"0005","productName":"2GB","productDescription":"testproduct12"}]}]}}
   
Here my requirement is to get all the id's from table which is having productId (0001,0005). I tried below sql:

    SELECT x.id 
    FROM  (
        SELECT json_array_elements("json" -> 'test' -> 'details'->'products') AS products,"id"
        FROM   table_test b
           
       ) x
    WHERE  x.products ->> 'productId' in ('0001','0005');

But it always returns empty dataset. Can anyone please guide me to correct the above statement

Asked by Karthika (23 rep)

Jul 31, 2023, 05:14 PM
Last activity: Aug 1, 2023, 12:15 AM

json_array_elements returns empty dataset in postgres

Related Questions