Search PSQL JSONB array of tuples

user_2352611 0
Viewed 83

I have a JSONB column "deps" like this

[
    [{
        "name": "A"
    }, "823"],
    [{
        "name": "B"
    }, "332"],
    [{
        "name": "B"
    }, "311"]
]

I want to set a column "stats" to NULL for all rows where the JSON array in the column "deps" contains a tuple with "name" "B". In the example above the column "deps" has two such tuples. Is it possible?

The dictionary {"name": "B"} always comes first in the tuple.

Would the same search in this JSON be faster:

[{
        "id": {
            "name": "A"
        },
        "value": "823"
    }, {
        "id": {
            "name": "B"
        },
        "value": "332"
    },
    {
        "id": {
            "name": "B"
        },
        "value": "311"
    }
]
postgresql json
2 Answers

You could use the @? psql JSON operator in combination with jsonpath to check if your JSONB blob contains any {"name": "B"} for any first value of all occurring tuples.

Here is an example with the JSONB blob from the stated question:

-- returns 'true' if any first tuple value contains an object with key "name" and value "B"
SELECT '[[{"name": "A"}, "823"], [{"name": "B"}, "332"], [{"name": "B"}, "311"]]'::JSONB @? '$[*][0].name ? (@ == "B")';

Now you can combine this with your UPDATE logic:

UPDATE my_table
SET stats = NULL
WHERE deps @? '$[*][0].name ? (@ == "B")';
user_48099320

You can use the contains operator @> if the content has the structure of your first example

update the_table
   set stats = null
where deps @> '[[{"name": "B"}]]'

For the structure in the second example, you would need to use:

where deps @> '[{"id": {"name": "B"}}]'
user_3303150
Related