PostgreSQL 9.4：在JSON字段数组中聚合/连接表格的id

Question

假设您有以下表格，比如：

表格：content_type

• id serial 非空
• tabs json

表格：data_type

• id serial 非空
• html 文本

这只是一个小例子来说明。

存储在tabs中的json可能像这样：

[
    {
        "name": "tab1",
        "properties": 
        [{
            "name": "prop1",
            "order": 1,
            "help_text": "help text",
            "description": "description",
            "data_type": 1
        }]
    },
    {
        "name": "tab2",
        "properties":[{
            "name": "prop2",
            "order": 1,
            "help_text": "help text2",
            "description": "description2",
            "data_type": 1
        },
        {
            "name": "prop3",
            "order": 2,
            "help_text": "help text3",
            "description": "description3",
            "data_type": 1
        }]
    }
]

现在我想要实现的是像以下伪代码一样进行连接：

SELECT content_type.id, content_type.tabs, data_type.id, data_type.html
FROM content_type
JOIN data_type
ON data_type.id = content_type.tabs::json->'data_type'::int

实际上，data_type.id和data_type.html是与选项卡属性的data_type连接在一起，而不像通常的联接查询中那样作为单独的列。

基本上，我要寻找的结果就好像是将2个或更多表通过id列连接起来一样，只是在这种情况下，“表”之一位于json对象的数组内。

是的，我知道上面的联接尝试非常偏离正题，因为我想要将附加属性“添加/追加”到修改过的选项卡json列的版本中，而不是作为额外的单独列。

在这种情况下，联接将在选择/联接查询中返回选项卡json数组中的id，并使用联接获取的附加属性进行扩展，因此它不仅仅是一个“data_type”:1，而是返回类似于以下内容：

"data_type": {
    "id":1, 
    "html": "<input type='text' id='%v' class='%v' placeholder='%v' value='%v' ng-model='%v'>"
}

......当然还有其他的属性。这仅仅是一个简单的例子，用于说明问题。

[
    {
        "name": "tab1",
        "properties": 
        [{
            "name": "prop1",
            "order": 1,
            "help_text": "help text",
            "description": "description",
            "data_type": {
                "id":1, 
                "html": "<input type='text' id='%v' class='%v' placeholder='%v' value='%v' ng-model='%v'>"
                ... and of course additional columns fetched from the data_type table, and added to the select return in our join, to manipulate the original json array of tabs->properties->data_type
            }
        }]
    },
    {
        "name": "tab2",
        "properties":[{
            "name": "prop2",
            "order": 1... etc

希望我说的有道理，你可以帮我解决问题吗？因为我好像有些卡住了。

顺便提一下，我在使用最新的9.4beta3。

我在这里找到了一个链接，让我对于PostgreSQL能够实现这个功能充满了希望：http://www.slideshare.net/EnterpriseDB/no-37327319（请看第17页）。

其他可能有用的链接：

- http://michael.otacoo.com/postgresql-2/manipulating-jsonb-data-with-key-unique/ - http://hashrocket.com/blog/posts/faster-json-generation-with-postgresql - PostgreSQL 9.2 row_to_json() with nested joins - http://info.enterprisedb.com/rs/enterprisedb/images/EDB_White_Paper_Using_the_NoSQL_Features_in_Postgres.pdf（第13页）

---

我试着做了一些实验，这是我目前的进展：

SELECT content_type.id, content_type.tabs as original, gf.json_agg as new_tabs
FROM content_type,
LATERAL (
    select json_agg(row1) from((
    select y.name, ss.extended_properties
    from json_to_recordset(
        (
            select * 
            from json_to_recordset(
                (
                    SELECT json_agg(ggg)
                    from(
                        SELECT tabs
                        FROM 
                        (   
                            SELECT 
                            *
                            FROM content_type as ct
                            WHERE ct.id=content_type.id
                        ) dsfds
                    )ggg
                )
            ) as x(tabs json)
        )
    ) as y(name text, properties json),
    LATERAL (
        select json_agg(row) as extended_properties
        from(
            select name, "order", data_type, data_type.html as data_type_html, help_text, description
            from json_to_recordset(properties) 
            as k(name text, "order" int, data_type int, help_text text, description text)
            JOIN data_type
            ON data_type.id = k.data_type
            )row
    ) ss
    ))row1
) gf

这导致以下结果（在浏览器中放大以查看图像中的文本-按住ctrl +滚轮向上或键盘上的加号）：

至少现在我可以将data_type.html放入其中，尽管我更喜欢 "data_type": {"id": 1, "html": "[somehtmlhere]"}。

由于某种原因，它不允许我将json_agg包装起来，并将输出显示为1个组合的json文档。不理解为什么，但猜想它与LATERAL有关，可能是PostgreSQL 9.4 beta3中的一个错误。

我知道肯定有更好的方法和解决方案 - 我对pgSQL或pg一般并不是很有经验......但，希望能够学到。

- Dac0d3r

---

我猜这个实现起来太复杂了，相比之下，简单地在data_type上创建一个触发器，每当保存更改时就会触发。然后，该触发器将查找所有content_types中的所有选项卡并更新嵌入式data_type。我想通过创建这个线程来避免的是避免这种嵌入式模型，并通过执行类似于在列id上连接表格的操作来获取嵌入式id中其余data_type字段的内容。 - Dac0d3r

Answer 1

基本思想是，您的查询应该期望您的json以某种方式结构化，否则它会变得非常复杂。 根据预期的结构，我们可以使用json_to_recordset将json结构解析为列，并使用json_build_object和json_agg重新构建它以添加其他信息。

WITH tab_properties_with_expanded_data_type AS (
    SELECT
      content_type.id AS content_type_id,
      tab.name AS tab_name,
      json_agg(
          -- re-build the property object, but with additional data_type information
          json_build_object(
              'name', property.name,
              'order', property.order,
              'help_text', property.help_text,
              'description', property.description,
              'data_type', json_build_object('id', data_type.id, 'html', data_type.html)
          )
      ) AS tab_properties
    FROM content_type,
      json_to_recordset(content_type.tabs) AS tab(name TEXT, properties JSON),
      json_to_recordset(tab.properties) AS property(name TEXT, "order" INTEGER, help_text TEXT, description TEXT, data_type INTEGER)
      LEFT JOIN data_type ON data_type.id = property.data_type
    GROUP BY 
      content_type.id, 
      tab.name
)
SELECT
  tab_properties_with_expanded_data_type.content_type_id AS id, 
  json_agg(
      -- rebuild the tab object
      json_build_object(
          'name', tab_properties_with_expanded_data_type.tab_name,
          'properties', tab_properties_with_expanded_data_type.tab_properties
      )
  )
FROM tab_properties_with_expanded_data_type
GROUP BY 
  tab_properties_with_expanded_data_type.content_type_id

这样做虽然能实现功能，但灵活性非常有限：我必须明确列出每个选项卡和属性的每个字段，并且我希望文档具有特定的结构。但这是一个良好的起点 :)

- Clément Prévost