Match two jsonb documents by order of elements in array

Question

I have table of data jsonb documents in postgres and second table containing templates for data.

I need to match data jsonb row with template jsonb row just by order of elements in array in effective way.

template jsonb document:

{
   "template":1,
   "rows":[
      "first row",
      "second row",
      "third row"
   ]
}

data jsonb document:

{
   "template":1,
   "data":[
      125,
      578,
      445
   ]
}

desired output:

Desc	Amount
first row	125
second row	578
third row	445

template table:

| id        | jsonb                                                  |
| --------  | ------------------------------------------------------ |
| 1         | {"template":1,"rows":["first row","second row","third row"]}           |
| 2         | {"template":2,"rows":["first row","second row","third row"]}           |
| 3         | {"template":3,"rows":["first row","second row","third row"]}           |

data table:

| id        | jsonb                                         |
| --------  | -------------------------------------------   |
| 1         | {"template":1,"data":[125,578,445]}           |
| 2         | {"template":1,"data":[125,578,445]}           |
| 3         | {"template":2,"data":[125,578,445]}           |

I have millions of data jsonb documents and hundreds of templates.

I would do it just by converting both to tables, then use row_number windowed function but it does not seem very effective way to me.

Is there better way of doing this?

Answer 1

You will have to normalize this mess "on-the-fly" to get the output you want.

You need to unnest each array using jsonb_array_elements() using the with ordinality option to get the array index. You can join the two tables by extracting the value of the template key:

Assuming you want to return this for a specific row from the data table:

select td.val, dt.val
from data 
  cross join jsonb_array_elements_text(data.jsonb_column -> 'data') with ordinality as dt(val, idx)
  left join template tpl 
         on tpl.jsonb_column ->> 'template' = data.jsonb_column ->> 'template'
  left join jsonb_array_elements_text(tpl.jsonb_column -> 'rows') with ordinality as td(val, idx) 
         on td.idx = dt.idx
where data.id = 1;

Online example