Postgres 13

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

CREATE TEMP TABLE my_table (id int, marc jsonb);
INSERT INTO my_table VALUES
  (1, '{
   "dynamicFields":[
      {
         "name":"200",
         "hidden":false,
         "subfields":[
            {
               "name":"a",
               "value":"Subfield a"
            },
            {
               "name":"b",
               "value":"Subfield b"
            }           
         ]
      }
   ]
}')
,   (2, '{
   "dynamicFields":[
      {
         "name":"300",
         "hidden":false,
         "subfields":[
            {
               "name":"a",
               "value":"Subfield a"
            },
            {
               "name":"b",
               "value":"Subfield b"
            }           
         ]
      }
   ]
}')

,   (3, '{
   "dynamicFields":[
      {
         "name":"200",
         "hidden":false,
         "subfields":[
            {
               "name":"b",
               "value":"Subfield b"
            }           
         ]
      }
   ]
}');

CREATE TABLE



INSERT 0 3


SELECT *
FROM   my_table
WHERE  marc->'dynamicFields' @> '[{"name": "200", "subfields":[{"name": "a"}]}]'


id
marc


1
{"dynamicFields": [{"name": "200", "hidden": false, "subfields": [{"name": "a", "value": "Subfield a"}, {"name": "b", "value": "Subfield b"}]}]}



SELECT 1


SELECT *
FROM   my_table
WHERE  marc->'dynamicFields' @> '[{"name": "200"}]'
AND    marc->'dynamicFields' @> '[{"subfields":[{"name": "a"}]}]';


id
marc


1
{"dynamicFields": [{"name": "200", "hidden": false, "subfields": [{"name": "a", "value": "Subfield a"}, {"name": "b", "value": "Subfield b"}]}]}



SELECT 1


-- for comparison: queries with only one filter:
SELECT *
FROM   my_table
WHERE  marc->'dynamicFields' @> '[{"name": "200"}]';


id
marc


1
{"dynamicFields": [{"name": "200", "hidden": false, "subfields": [{"name": "a", "value": "Subfield a"}, {"name": "b", "value": "Subfield b"}]}]}

3
{"dynamicFields": [{"name": "200", "hidden": false, "subfields": [{"name": "b", "value": "Subfield b"}]}]}



SELECT 2


SELECT *
FROM   my_table
WHERE  marc->'dynamicFields' @> '[{"subfields":[{"name": "a"}]}]';


id
marc


1
{"dynamicFields": [{"name": "200", "hidden": false, "subfields": [{"name": "a", "value": "Subfield a"}, {"name": "b", "value": "Subfield b"}]}]}

2
{"dynamicFields": [{"name": "300", "hidden": false, "subfields": [{"name": "a", "value": "Subfield a"}, {"name": "b", "value": "Subfield b"}]}]}



SELECT 2

id	marc
1	{"dynamicFields": [{"name": "200", "hidden": false, "subfields": [{"name": "a", "value": "Subfield a"}, {"name": "b", "value": "Subfield b"}]}]}
3	{"dynamicFields": [{"name": "200", "hidden": false, "subfields": [{"name": "b", "value": "Subfield b"}]}]}

id	marc
1	{"dynamicFields": [{"name": "200", "hidden": false, "subfields": [{"name": "a", "value": "Subfield a"}, {"name": "b", "value": "Subfield b"}]}]}
2	{"dynamicFields": [{"name": "300", "hidden": false, "subfields": [{"name": "a", "value": "Subfield a"}, {"name": "b", "value": "Subfield b"}]}]}