Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE flat(id, data) AS (VALUES
  (1::int, $${
    "instances": [
      {
        "path": [
          {"id": "root", "index": 2},
          {"id": "folder1", "index": 0},
          {"id": "folder2", "index": 0},
          {"id": "file X", "index": 0}
        ],
        "pdf": "pdf in 1,2,3",
        "info": "some other data"
      },
      {
        "path": [
          {"id": "root", "index": 2},
          {"id": "folder1", "index": 0},
          {"id": "folder2", "index": 0},
          {"id": "file Y", "index": 1}
        ],
        "jpg": "jpg in 1,2,3",
        "info": "some other data"
      }
    ]
  }$$::jsonb)
);

SELECT 1


SELECT *
FROM "flat" f
WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements(f.data -> 'instances') AS instance,
  jsonb_to_record(instance) AS _i(path jsonb[]),
  unnest(path) WITH ORDINALITY AS ancestor(element, idx)
  WHERE element->>'id' = 'folder2'
  GROUP BY path[0:ancestor.idx]
  HAVING jsonb_agg(instance) @> '[{"pdf": "pdf in 1,2,3"}, {"jpg": "jpg in 1,2,3"}]'::jsonb
)


id
data


1
{"instances": [{"pdf": "pdf in 1,2,3", "info": "some other data", "path": [{"id": "root", "index": 2}, {"id": "folder1", "index": 0}, {"id": "folder2", "index": 0}, {"id": "file X", "index": 0}]}, {"jpg": "jpg in 1,2,3", "info": "some other data", "path": [{"id": "root", "index": 2}, {"id": "folder1", "index": 0}, {"id": "folder2", "index": 0}, {"id": "file Y", "index": 1}]}]}



SELECT 1


SELECT id, to_jsonb(ancestor_path) AS ancestor_path, instances
FROM "flat" f,
LATERAL (
  SELECT element->>'id' AS ancestor_name, path[0:ancestor.idx] AS ancestor_path, jsonb_agg(instance) AS instances
  FROM jsonb_array_elements(f.data -> 'instances') AS instance,
  jsonb_to_record(instance) AS _i(path jsonb[]),
  unnest(path) WITH ORDINALITY AS ancestor(element, idx)
  GROUP BY path[0:ancestor.idx], element->>'id'
) AS data
WHERE ancestor_name = 'folder2'
  AND instances @> '[{"pdf": "pdf in 1,2,3"}, {"jpg": "jpg in 1,2,3"}]'::jsonb


id
ancestor_path
instances


1
[{"id": "root", "index": 2}, {"id": "folder1", "index": 0}, {"id": "folder2", "index": 0}]
[{"pdf": "pdf in 1,2,3", "info": "some other data", "path": [{"id": "root", "index": 2}, {"id": "folder1", "index": 0}, {"id": "folder2", "index": 0}, {"id": "file X", "index": 0}]}, {"jpg": "jpg in 1,2,3", "info": "some other data", "path": [{"id": "root", "index": 2}, {"id": "folder1", "index": 0}, {"id": "folder2", "index": 0}, {"id": "file Y", "index": 1}]}]



SELECT 1


SELECT *
FROM "flat" f
WHERE EXISTS (
  WITH instances AS (
    SELECT value, element->>'id' AS ancestor_name, path[0:ancestor.idx] AS ancestor_path
    FROM jsonb_array_elements(f.data -> 'instances') AS el(value),
    jsonb_to_record(value) AS _i(path jsonb[]),
    unnest(path) WITH ORDINALITY AS ancestor(element, idx)
  )
  SELECT *
  FROM instances a JOIN instances b USING (ancestor_path, ancestor_name)
  WHERE ancestor_name = 'folder2'
    AND a.value @> '{"pdf": "pdf in 1,2,3"}'
    AND b.value @> '{"jpg": "jpg in 1,2,3"}'
)


id
data


1
{"instances": [{"pdf": "pdf in 1,2,3", "info": "some other data", "path": [{"id": "root", "index": 2}, {"id": "folder1", "index": 0}, {"id": "folder2", "index": 0}, {"id": "file X", "index": 0}]}, {"jpg": "jpg in 1,2,3", "info": "some other data", "path": [{"id": "root", "index": 2}, {"id": "folder1", "index": 0}, {"id": "folder2", "index": 0}, {"id": "file Y", "index": 1}]}]}



SELECT 1


SELECT id, ancestor_path, instances
FROM "flat" f,
LATERAL (
  SELECT to_jsonb(path[0:ancestor.idx]) AS ancestor_path, jsonb_agg(instance) AS instances
  FROM jsonb_array_elements(f.data -> 'instances') AS instance,
  jsonb_to_record(instance) AS _i(path jsonb[]),
  unnest(path) WITH ORDINALITY AS ancestor(element, idx)
  GROUP BY path[0:ancestor.idx]
) AS data
WHERE ancestor_path->-1->>'id' = 'folder2'
  AND instances @> '[{"pdf": "pdf in 1,2,3"}, {"jpg": "jpg in 1,2,3"}]'::jsonb


id
ancestor_path
instances


1
[{"id": "root", "index": 2}, {"id": "folder1", "index": 0}, {"id": "folder2", "index": 0}]
[{"pdf": "pdf in 1,2,3", "info": "some other data", "path": [{"id": "root", "index": 2}, {"id": "folder1", "index": 0}, {"id": "folder2", "index": 0}, {"id": "file X", "index": 0}]}, {"jpg": "jpg in 1,2,3", "info": "some other data", "path": [{"id": "root", "index": 2}, {"id": "folder1", "index": 0}, {"id": "folder2", "index": 0}, {"id": "file Y", "index": 1}]}]



SELECT 1


SELECT id, ancestor_path, instances
FROM "flat" f,
LATERAL (
  SELECT (
    SELECT jsonb_agg(element) FILTER (WHERE idx <= searched_index)
    FROM (
      SELECT *, MIN(idx) FILTER (WHERE element @> '{"id": "folder2"}') OVER () AS searched_index
      FROM jsonb_array_elements(instance -> 'path') WITH ORDINALITY anc(element, idx)
    ) AS path
  ) AS ancestor_path, jsonb_agg(instance) AS instances
  FROM jsonb_array_elements(f.data -> 'instances') AS instance
  GROUP BY ancestor_path
) AS data
WHERE instances @> '[{"pdf": "pdf in 1,2,3"}, {"jpg": "jpg in 1,2,3"}]'::jsonb


id
ancestor_path
instances


1
[{"id": "root", "index": 2}, {"id": "folder1", "index": 0}, {"id": "folder2", "index": 0}]
[{"pdf": "pdf in 1,2,3", "info": "some other data", "path": [{"id": "root", "index": 2}, {"id": "folder1", "index": 0}, {"id": "folder2", "index": 0}, {"id": "file X", "index": 0}]}, {"jpg": "jpg in 1,2,3", "info": "some other data", "path": [{"id": "root", "index": 2}, {"id": "folder1", "index": 0}, {"id": "folder2", "index": 0}, {"id": "file Y", "index": 1}]}]



SELECT 1