Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE guest_group(id, custom_fields) AS VALUES
  (1, '[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name"]},'
       '{"value": "118", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]}]'::jsonb),
  (2, '[{"value": "Herr Anton Test", "display_name": "Name", "servicio_tags": ["full-name"]},'
       '{"value": "117", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]}]'),
  (3, '[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["wrong-name"]}]'),
  (4, '[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name", "alt-name"]}]'),
  (5, '[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": []}]'),
  (6, '[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name"]},'
       '{"value": "118", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]},'
       '{"value":"likes JSONB", "display_name":"Traces", "servicio_tags": ["trace"]}]');
  
  TABLE guest_group
6 rows affected



id
custom_fields


1
[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name"]}, {"value": "118", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]}]

2
[{"value": "Herr Anton Test", "display_name": "Name", "servicio_tags": ["full-name"]}, {"value": "117", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]}]

3
[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["wrong-name"]}]

4
[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name", "alt-name"]}]

5
[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": []}]

6
[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name"]}, {"value": "118", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]}, {"value": "likes JSONB", "display_name": "Traces", "servicio_tags": ["trace"]}]


SELECT *
FROM guest_group
WHERE '[{"value": "Frau Barbara Test","servicio_tags": ["full-name"]}]'::jsonb <@ custom_fields


id
custom_fields


1
[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name"]}, {"value": "118", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]}]

4
[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name", "alt-name"]}]

6
[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name"]}, {"value": "118", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]}, {"value": "likes JSONB", "display_name": "Traces", "servicio_tags": ["trace"]}]


SELECT *
FROM guest_group
WHERE EXISTS(
  SELECT 1
  FROM jsonb_array_elements(custom_fields) AS el
  WHERE el->>'value' = 'Frau Barbara Test'
    AND el->'servicio_tags' = '["full-name"]'::jsonb)


id
custom_fields


1
[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name"]}, {"value": "118", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]}]

6
[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name"]}, {"value": "118", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]}, {"value": "likes JSONB", "display_name": "Traces", "servicio_tags": ["trace"]}]


SELECT *
FROM guest_group
WHERE '{"value": "Frau Barbara Test"}' <@ ANY(
  SELECT el
  FROM jsonb_array_elements(custom_fields) AS el
  WHERE el->'servicio_tags' = '["full-name"]'::jsonb)


id
custom_fields


1
[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name"]}, {"value": "118", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]}]

6
[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name"]}, {"value": "118", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]}, {"value": "likes JSONB", "display_name": "Traces", "servicio_tags": ["trace"]}]

id	custom_fields
1	[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name"]}, {"value": "118", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]}]
2	[{"value": "Herr Anton Test", "display_name": "Name", "servicio_tags": ["full-name"]}, {"value": "117", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]}]
3	[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["wrong-name"]}]
4	[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name", "alt-name"]}]
5	[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": []}]
6	[{"value": "Frau Barbara Test", "display_name": "Name", "servicio_tags": ["full-name"]}, {"value": "118", "display_name": "Zimmernummer", "servicio_tags": ["room-number"]}, {"value": "likes JSONB", "display_name": "Traces", "servicio_tags": ["trace"]}]