PostgreSQL 9.4:数组中JSON字段ID上的聚集/联接表

本文介绍了PostgreSQL 9.4:数组中JSON字段ID上的聚集/联接表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

假设您有以下表格，例如

表:content_type

id序列号不为空
tabs json

表:data_type

id序列号不为空
html文字

这只是一个很小的例子.

选项卡中存储的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对象数组内. /p>

是的，我知道上述连接尝试还很遥远，因为我想将其他属性添加/附加"到tabs json列的修改版本中，而不是作为其他单独的列.

在这种情况下，联接将返回-在选择/联接查询中-tabs 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

希望这是有道理的，并且您可以为我提供帮助，因为我似乎有些困惑.

Ps.使用最新的9.4beta3 btw.

我在这里找到了一个链接，使我希望这确实可以通过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()
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 + mwheel up og和键盘上的键):

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

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

我知道必须有一种更好的方法和解决方案-我对pgSQL或pg的经验还不是很丰富...

解决方案

基本思想是您的查询应期望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

这可行，但是在灵活性方面非常有限:我必须明确列出选项卡和属性的每个字段，并且我希望文档具有特定的结构.但这是一个好的开始:)

Suppose you have the following tables, say

table: content_type

id serial not null
tabs json

table: data_type

id serial not null
html text

This is just a tiny example to illustrate.

The json stored in tabs could be like eg:

[
    {
        "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
        }]
    }
]

What I'm looking to achieve now is to make a join like the following pseudo code:

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

Where data_type.id, data_type.html is actually joined to the tabs' properties' data_type and not as a seperate column like in the above usual join query.

Basically I'm looking for the same result as one would have if it was 2 or more tables joined together on column id's, except that one of the "tables" in this case is located inside an array of json objects.

And yes I know the above join attempt is very far off, since I want to "add/append" the additional attributes to a modified version of the tabs json column, not as additional seperate columns.

In this case the join would return - in the select/join query - the id inside the tabs json array and extend it with the join-fetched additional attributes, so instead of simply a "data_type": 1 it would return something like:

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

... and additional attributes of course. This is just a simple example to illustrate the problem.

[
    {
        "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

Hope this makes sense and that you can help me with this, because I seem to be somewhat stuck.

Ps.Using the latest 9.4beta3 btw.

I found a link here that give me hopes that this is indeed possible to achieve with PostgreSQL: http://www.slideshare.net/EnterpriseDB/no-37327319 (see slide 17)

Other links that might be helpful:

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(page 13)

I tried experimenting a bit - here's what I have so far:

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

which results in the following (zoom in in your browser to read the text in the image - hold ctrl + mwheel up og plus key on keyboard):

At least now I can put in the data_type.html in there, although I would have preferred "data_type": { "id": 1, "html": "[somehtmlhere]"}

For some reason it wont allow me to wrap json_agg around it and show you the output as 1 combined json document. Don't udnerstand why, but guess it has to do with LATERAL and it probably is a bug in PostgreSQL 9.4 beta3

I know there must be a way better approach and solution to this - I'm not very experienced with pgSQL or pg in general... yet.

解决方案

The basic idea is that your query should expect your json to be structured in a certain way, otherwise it gets really complex.Based on the expected structure, we are able to dissect the json structure into columns using json_to_recordset and to rebuild it with additional information using json_build_object and 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

This works but is very limited in terms of flexibility: I have to explicitly list every field of your tabs and properties and I expect the document to have a specific structure. But it's a good start :)

这篇关于PostgreSQL 9.4:数组中JSON字段ID上的聚集/联接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！