0

I have the following array JSON data structure:

{ arrayOfObjects: 
    [
        {
            "fieldA": "valueA1", 
            "fieldB": { "fieldC": "valueC", "fieldD": "valueD" }
        },
        {
            "fieldA": "valueA", 
            "fieldB": { "fieldC": "valueC", "fieldD": "valueD" }
        }
    ]
}

I would like to select all records where fieldD matches my criteria (and fieldC is unknown). I've see similar answers such as Query for array elements inside JSON type but there the field being queried is a simple string (akin to searching on fieldA in my example) where my problem is that I would like to query based on an object within an object within the array.

I've tried something like select * from myTable where jsonData -> 'arrayOfObjects' @> '[ { "fieldB": { "fieldD": "valueD" } } ]' ) but that doesn't seem to work.

How can I achieve what I want?

Ted
  • 73
  • 7

1 Answers1

1

You can execute a "contains" query on the JSONB field directly and pass the minimum you're looking for:

SELECT *
FROM mytable
WHERE json_data @> '{"arrayOfObjects": [{"fieldB": {"fieldD": "valueD"}}]}'::JSONB;

This of course assumes that fieldD is always nested under fieldB, but that's a fairly low bar to clear in terms of schema consistency.

dmfay
  • 2,417
  • 1
  • 11
  • 22