I went with final query, as seen here: https://dbfiddle.uk/gwzGZX8j
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;
So I added sorting, since I need to return NULL for not found identifiers. I also improved the way I provide the input into the query to drop the nested arrays and value duplication. I also decided to go with this where clause, because it moves all the conditional logic to the final step, and I believe is easier to read.
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;
Thank you for help