POSTGRES JSONB Select Value from Nested Array

Question

I am trying to get the opportunity equals resales from the following JSONB array in Postgres. But I can't seems to figure it out.

{
    "done": true,
    "size": 106,
    "records": [{
        "Name": "FEF",
        "IsActive": true,
        "attributes": {
            "price": "3",
            "width": "20"
        },
        "Description": null,
        "Opportunity": "Resale"
    }, {
        "Name": "DHQ",
        "IsActive": true,
        "attributes": {
            "price": "300",
            "width": "10000"
        },
        "Description": null,
        "Opportunity": "Resale"
    }]
}

SELECT  salesdata 
FROM public.salesdata 
where salesdata -> 0 ->> '"records":[{"Opportunity":"Resale"}]';

Error:

SQL Error [42804]: ERROR: argument of WHERE must be type boolean, not type text

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2019-12-31T11:33:24

The ->> operator returns the content of the element specified. If you want to test for the presence of a value, use the contains operator @>

Also the top level JSON you have isn't an array, so salesdata -> 0 doesn't make sense.

To test if the array identified by the key records contains at least one key/value pair with specific value, you can use:

SELECT salesdata 
FROM public.salesdata 
where salesdata -> 'records' @> '[{"Opportunity":"Resale"}]';

POSTGRES JSONB Select Value from Nested Array

1 Answers