0

I've a nested array to represent UI from a dynamic report template, but this array (JSONB) contains UUID for each field. I needed to transform this nested array with UUID into a same structure array but with the object for the respective fields.

EG: report_template:[[UUID, UUID], [UUID, UUID], UUID]

Expected outcome: [[{id: UUID, type: 'INPUT', label: 'last Name' }, {id: UUID, type: 'NUMBER', label: 'Age'}], [{id: UUID, type: 'DATE', label: 'Date'}, {id: UUID, type: 'INPUT', label: 'first Name'}], {id: UUID, type: 'INPUT', label: 'middle name'}]

I'm using a Postgres database with version 12, and Hasura for backend.

UPDATED: Here's a bit more details to help understand the structure.

I've a table called report_template_fields with this structure:

{ id: 13ea9020-0013-4ab6-b22f-002cddb33c63, name: "First Name", type: TEXT }, { id: 13ea9020-0013-4ab6-b22f-002cddb33c63, name: "Last Name", type: TEXT }

While I'm using a jsonB in other table to keep the structure from the report template, called report_template

{ id: cd72bca9-9d9c-4386-8b4e-00e8e29c4a3a, name: "Default Template", fields: [["13ea9020-0013-4ab6-b22f-002cddb33c63", "5d368103-7400-477a-8f3c-ed51c647cb7c"] }

Wanted to build a computed function to give the relationships between report_template_fields to report_template.

Where fields would be: [[{ id: 13ea9020-0013-4ab6-b22f-002cddb33c63, name: "First Name", type: TEXT }, { id: 13ea9020-0013-4ab6-b22f-002cddb33c63, name: "Last Name", type: TEXT }']]

Dinarte Jesus
  • 1
  • 1
  • You need to provide some real sample data. I for one have no idea how you get from five "uuid" values to a list of key value pairs. Where does e.g. `last name` or `INPUT` come from? It would also be much more helpful to use real values instead of `UUID` all the time (or is that supposed to be a placeholder for the same actual value?) –  Jun 25 '22 at 12:09
  • Hi, just added more details to explain what I'm trying to achieve here. Let me know if it's clear. – Dinarte Jesus Jun 25 '22 at 15:56
  • "*Wanted to build a computed function*" - yes, build a recursive function for this. What are you struggling with? – Bergi Jun 25 '22 at 17:27
  • So the tables `template_fields` and `report_template_fields` contain one JSONB column with template definition rather than proper columns? –  Jun 25 '22 at 17:32

1 Answers1

0

Wanted to build a computed function to give the relationships between report_template_fields to report_template.

Since there are many fields for one report template, you need to create a table computed function. According to Hasura's rules for table computed functions, it will be need to:

  1. Take a record of report_templates as its first argument.
  2. Return a set of report_template_fields records.
  3. Be marked as STABLE.

The easiest way to handle the nested arrays is to use jsonb_array_elements to take the outer fields array of the template and generate a set of inner arrays. Then use JOIN LATERAL to take each inner array and pass it to jsonb_array_elements_text which will generate a row for each element of the inner array with a textual representation of the UUIDs contained within. You can then use the UUIDs to JOIN the report_template_fields table:

CREATE OR REPLACE FUNCTION template_fields(IN template report_templates) RETURNS SETOF report_template_fields AS $body$
    SELECT DISTINCT ON (rtf.id) -- do not return the same field multiple times
      rtf.*
    FROM
    -- unnest the top level JSON array and get the inner array .
    jsonb_array_elements(template.json_data['fields']) inner_arrs(inner_arr)
    --for each inner JSON array, unnest its elements (( as text.
    JOIN LATERAL jsonb_array_elements_text(inner_arr) uuid_texts(id) ON TRUE
    JOIN report_template_fields rtf ON uuid_texts.id::uuid = rtf.id
$body$ LANGUAGE SQL STABLE;
THX1138
  • 1,518
  • 14
  • 28