PostgreSQL query on a JSONB Array of Objects

Question

I've a PostgreSQL table with this complex structure :

"item": [{
        "field10",
        "field11",
        "field12": {
            "field20"
        },
        "field13": [{
                "field21": [{
                        "field30"
                    }
                ],
                "field22": [{
                        "field31"
                        "field32": {
                            "field40"
                        }
                    }
                ],
            }
        ]
    }]

And I've to make a query for filter until the field40 many explication than I saw are for objects mor simple. Can you explain me how can I do ?

Edit :

The example what I made i more simplest than reality. In reality the object is very very long. If I try to make a simple example with values, that's like that :

{
"uuid": "64fc7a55-be2d-41d8-99c1-d08fa5780731",
"item": [{
            "field10" : 2004,
            "field11" : false,
            "field12": {
                "field20" : "cc092aa1-6465-4526-9292-373344ed3e18",
                "field21" : "AMAZONE"
            },
            "field13": [{
                    "field22": [{
                            "field30" : "ee5bfd21-b2ce-453e-94a2-ef9c87002758",
                            "field31" : "MICROSOFTE"
                        },{
                            "field30" : "2eb39939-08b9-4456-9b5b-eff88f00bacb",
                            "field31" : "GOOGLEE"
                        }
                    ],
                    "field23": [{
                            "field32" : "e70bec99-689a-4c5d-ab81-e7601bc5b435",
                            "field33": {
                                "field40" : "a70ce09e-21ec-46cd-bfe6-591b84c0b328",
                                "field41" : "https://stackoverflow.com/"
                            }
                        },{
                            "field32" : "e70bec99-689a-4c5d-ab81-e7601bc5b435",
                            "field33": {
                                "field40" : "7bfa6882-2e65-4f96-b4a5-6aa0d148a95d",
                                "field41" : "https://gis.stackexchange.com/"
                            }
                        }
                    ],
                }
            ]
        }]
},
{
"uuid": "f131864a-338e-4f2c-91b1-13950958bd62",
"item": [{
            "field10" : 2012,
            "field11" : false,
            "field12": {
                "field20" : "2b8176e4-c858-4159-b279-d852df77c63b",
                "field21" : "PIXABAYE"
            },
            "field13": [{
                    "field22": [{
                            "field30" : "9e70b3d8-8f3f-4ae1-ba64-86efa513bd28",
                            "field31" : "IOBEYAE"
                        },{
                            "field30" : "0150dba2-98de-4143-bd81-329b74874403",
                            "field31" : "PINTERESTE"
                        }
                    ],
                    "field23": [{
                            "field32" : "3ab19111-513a-47db-9fb5-52dc0a1e1c4d",
                            "field33": {
                                "field40" : "7be95726-6698-4bdd-8f6b-2595d1704a2a",
                                "field41" : "https://google.com/"
                            }
                        },{
                            "field32" : "7993aba5-f13f-4998-af7b-9b0b8aec8aa9",
                            "field33": {
                                "field40" : "4740dbcf-84c4-40ff-a633-9207e79cabf4",
                                "field41" : "https://yahoo.com/"
                            }
                        }
                    ],
                }
            ]
        }]
}

In this example, my Postgres table have two columns, one with the primary key uuid and one with the object in jsonb item And what I want, for example is to make a filter on my items where "field41" contains value "gis.stackexchange.com" for et in this example if I search "field41" = "https://yahoo.com/" the tuple with "uuid" = "uuid": "64fc7a55-be2d-41d8-99c1-d08fa5780731" will not be in the result

user_6464308 · Answer

With PostgreSQL v12 and better, you can use the jsonpath language to write such a query:

SELECT uuid
FROM jtab
WHERE jsonb_path_exists(
         item,
         '$[*].field13[*].field23[*].field33.field41 ? (@ == "https://yahoo.com/")'
      );

user_3440110 · Answer

Your question suggests that you also need to accomplish a partial match on the URL in "field41". This may be accomplished by enumerating each nested array with jsonb_array_elements and then using WHERE ... LIKE ... to filter records matching the desired domain name:

SELECT
   t.data->>'uuid' as uuid,
   jfield23->'field33'->>'field40' as field40,
   jfield23->'field33'->>'field41' as field41
FROM
   jtest t,
   jsonb_array_elements(t.data->'item') as jitem,
   jsonb_array_elements(jitem->'field13') as jfield13,
   jsonb_array_elements(jfield13->'field23') as jfield23
WHERE jfield23->'field33'->>'field41' LIKE '%' || 'yahoo.com' || '%'

I've included the the relevant field40 and field41 in the SELECT clause for illustration purposes.

See the full example on db<>fiddle.

PostgreSQL query on a JSONB Array of Objects

2 Answers