79481004

Date: 2025-03-03 12:46:26
Score: 0.5
Natty:
Report link

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

Reasons:
  • Blacklisted phrase (0.5): Thank you
  • Blacklisted phrase (0.5): I need
  • Long answer (-1):
  • Has code block (-0.5):
  • Self-answer (0.5):
  • Low reputation (0.5):
Posted by: guruk