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

i am using version 12 of postgre

lancegoh
– lancegoh

2019-12-31 11:35:16 +00:00
Commented Dec 31, 2019 at 11:35 — lancegoh
– lancegoh, Commented Dec 31, 2019 at 11:35
What is the output you expect?

user330315
– user330315

2019-12-31 11:36:34 +00:00
Commented Dec 31, 2019 at 11:36 — user330315
– user330315, Commented Dec 31, 2019 at 11:36

user330315 · Accepted Answer · 2019-12-31 11:33:24Z

2

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"}]';

answered Dec 31, 2019 at 11:33

user330315

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

Collectives™ on Stack Overflow

POSTGRES JSONB Select Value from Nested Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related