Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 12.15 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit



SELECT 1


create table jtest (
  data jsonb
);

CREATE TABLE


insert into jtest (data) values ('{
"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/"
                            }
                        }
                    ]
                }
            ]
        }]
}');

INSERT 0 2


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' || '%'



uuid
field40
field41


f131864a-338e-4f2c-91b1-13950958bd62
4740dbcf-84c4-40ff-a633-9207e79cabf4
https://yahoo.com/



SELECT 1