如何将 UUID 的 SQL JSONB 嵌套数组转换为具有对象的相同数组

Question

我有一个嵌套数组来表示动态报告模板中的 UI，但此数组 (JSONB) 包含每个字段的 UUID。我需要将这个带有 UUID 的嵌套数组转换为相同的结构数组，但具有相应字段的对象。

EG：report_template：[[UUID, UUID], [UUID, UUID], UUID]

预期成果：[[{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'}]

我正在使用版本为 12 的 Postgres 数据库，并将 Hasura 用于后端。

更新：这里有一些细节来帮助理解结构。

我有一个名为report_template_fields的表，具有以下结构：

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

当我在其他表中使用 jsonB 来保留报告模板中的结构时，称为report_template

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

想构建一个计算函数来提供report_template_fields之间的关系report_template。

字段的位置：[[{ id: 13ea9020-0013-4ab6-b22f-002cddb33c63, name: "First Name", type: TEXT }, { id: 13ea9020-0013-4ab6-b22f-002cddb33c63, name: "Last Name", type: TEXT }']]

Answer 1

想要构建一个计算函数来提供report_template_fields之间的关系report_template。

由于一个报告模板有多个字段，因此您需要创建一个表计算函数。根据 Hasura 的表计算函数规则，需要：

1. 以report_templates的记录作为其第一个论点。
2. 返回一组report_template_fields记录。
3. 标记为稳定。

处理嵌套数组的最简单方法是使用jsonb_array_elements获取模板的外部fields数组并生成一组内部数组。然后使用JOIN LATERAL获取每个内部数组并将其传递给jsonb_array_elements_text这将为内部数组的每个元素生成一行，其中包含 UUID 的文本表示形式。然后，您可以使用 UUIDJOINreport_template_fields表：

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;