0

I want to be able to reference the error table for msg and description based on the err_id on the results table for err_map jsonb column, I'd also want to be able relate which error occurred against which column whether the independent columns c1,c2 or val_map jsonb column c3, c4)

the only reason the val_map stores data(with .) as "val_map.c3": 3 so we can identify that these columns were from val_map when mapping errors to columns.

I have a result table here the err_map column values 1,3 reference to below error table

   id    |   c1    |  c2     | val_map              | err_map
----------------------------------------------------------------
    1    |   chk1  |  chk2   | {"c3":3, "c4":4}     | {"c1": 1, "val_map.c3": 3}

Error Table

   id    |   msg   |  description
----------------------------------------------------------------
    1    |   msg1  |  an error1 occurred
----------------------------------------------------------------
    3    |   msg3  |  an error3 occurred

I looked at jsonb_each and jsonb_object_keys but can't really figure out how to use it to join these tables. Any help/hints will be appreciated. Pardon if something is unclear, please ask to provide more detail.

[Edit 1]: removed foreign key reference as it was misleading

[Edit 2]: I've got it working but it's quite inefficient

select
  e.error_key,
  e.error_message,
  T2.key as key
from result.error e
inner join (
  select
    substring(T1.key, 11) as key,
    T1.value
  from (
    select em.key, em.value
    from result rd, jsonb_each(rd.error_map) as em
  ) as T1
  where T1.key like '%value_map%'
union all
  select T1.key , T1.value
  from (
    select em.key, em.value
    from result rd, jsonb_each(rd.error_map) as em
  ) as T1
  where T1.key not like '%value_map%'
) as T2 on T2.value::bigint = e.id;
Bergi
  • 630,263
  • 148
  • 957
  • 1,375
Saurabh Sharma
  • 489
  • 5
  • 20
  • You cannot create a foreign key from a part of a column, and not form multiple parts either. You'll need to normalise your schema. – Bergi Jan 12 '23 at 20:37
  • Thanks @Bergi I have been able to do so using joins and jsonb_each but it's very inefficient. select e.error_key , e.error_message, T2.key as key from result.error e inner join (select substring(T1.key, 11) as key, T1.value from (select em.key, em.value from result rd, jsonb_each(rd.error_map) as em) as T1 where T1.key like '%value_map%' union all select T1.key , T1.value from (select em.key, em.value from result rd, jsonb_each(rd.error_map) as em) as T1 where T1.key not like '%value_map%') as T2 on T2.value::bigint = e.id; – Saurabh Sharma Jan 13 '23 at 15:44
  • Ah you don't want a foreign key. What exactly do you mean by "reference", what query result do you expect? Please add the query you have and want to optimise as formatted text in your question – Bergi Jan 13 '23 at 16:13
  • Thanks. That doesn't look too bad/inefficient, but I don't understand what the `key like '%value_map%'` and `substring(T1.key, 11)` are about - they do not seem motivated by the example data you posted? – Bergi Jan 13 '23 at 18:15
  • So the idea here is, the error map column can keep a k:v pair mentioning the column (whether individual or part of val_map column) as k and error_id as v. Now when I join tables I'd want to know what error msg and description was for each k which is okay for individual columns but not clear for val_map columns. For errors I need to know if the error relates to individual or val_map column. "val_map." is an identifier I'm using to know if a column is individual or part of val_map – Saurabh Sharma Jan 16 '23 at 09:10
  • Is it `value_map` or `val_map`? – Bergi Jan 16 '23 at 11:04
  • "*I need to know if the error relates to individual or val_map column.*" - so why are you trying to strip off the `val(ue)_map.` prefix from the key if you still want to distinguish them? And why do you think this was important for the JOIN? – Bergi Jan 16 '23 at 11:05
  • @Bergi *so why are you trying to strip off the val(ue)_map* and thanks for questioning this, I realized I was designing it all very wrong & inefficient. *And why do you think this was important for the JOIN?* It was totally because of my un-awareness of switches in SQL, I was trying to get both val_map and non val_map columns and doing the stripping for value_map values and combining the results. I've designed this differently now, but as your answer certainly performs better and gives solution to mentioned problem and I learned something from this, So I'm accepting it. – Saurabh Sharma Jan 16 '23 at 14:38

1 Answers1

1

You can simplify that UNION ALL to just

select
  e.error_key,
  e.error_message,
  T2.key as key
from result.error e
inner join (
  select
    case when T1.key like 'val_map.%'
      then substring(T1.key, 9)
      else T1.key
    end as key,
    T1.value
  from result rd, jsonb_each(rd.error_map) as T1
) as T2 on T2.value::bigint = e.id;
Bergi
  • 630,263
  • 148
  • 957
  • 1,375