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:

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?

CodePudding user response：

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