Postgres 16

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

CREATE TABLE

INSERT 0 3

WITH scheme_and_id as (
                select     (id_json -> 'identifier') AS queried_id,
                           trim((id_json-> 'scheme')::text, '"')AS scheme,
                           ordinal
                from jsonb_array_elements('[
      {"identifier": "XS12", "scheme" : "isin"},
      {"identifier": 1234, "scheme" : "valor"},
      {"identifier": "EXTRA", "scheme" : "isin"}
    ]'::jsonb) WITH ORDINALITY as f(id_json, ordinal)),
                 -- first build the JSON to be used to match the index
                 resolve_id as (select -- select the JSON objects
                                       id,
                                       (blob -> 'identifiers')     as "ids",
                                       ordinal,
                                       queried_id,
                                       scheme
                                 from blobstable, scheme_and_id
                                where (blob -> 'identifiers')  @>  jsonb_build_array( jsonb_build_object(scheme, queried_id))),
                 candidates as (
                     SELECT
                         id,
                         ordinal,
                         scheme,
                         queried_id,
                         (identifier_row -> scheme) as candidate_id,
                         (identifier_row -> 'primary')::boolean as "primary",
                         (identifier_row -> 'linked')::boolean as "linked"
                     FROM resolve_id ,
                          LATERAL jsonb_array_elements("ids") identifier_row
                 )
            SELECT id
            from candidates c
                     right join scheme_and_id on c.ordinal = scheme_and_id.ordinal
                and ((c."primary" and c.queried_id = c.candidate_id)                                            -- first rule
                or  (c."linked"   and c.queried_id = c.candidate_id
                    and not exists(select "id" from candidates where c.ordinal = candidates.ordinal and candidate_id is not null and "primary"))  -- second rule
                or (("primary" is Null or False) and ("linked" is Null or False) and c.queried_id = c.candidate_id
                    and not exists(select "id" from candidates where c.ordinal = candidates.ordinal and candidate_id is not null and "linked")))  -- third rule
            order by scheme_and_id.ordinal;

id
3
1
1
2

SELECT 4