sql - 尝试构造 PostgreSQL 查询以从 JSON 中提取对象、数组、对象、数组、对象中的文本值

问题描述

我正在构建 PostgreSQL 系统和 SQL Server 系统之间的接口，并试图“扁平化” JSON 数据的结构以促进这一点。我在 SQL Server 方面非常有经验，但我对 PostgreSQL 和 JSON 都是新手。

JSON本质上包含两种类型的结构：type“text”或“textarea”的结构，其中我想要的值在一个名为的对象中value（下面的前两种情况），以及type“select”的结构，其中value对象指向一个id对象较低级别的options数组（下面的第三种情况）。

{
    "baseGroupId": {
        "fields": [
            {
                "id": "1f53",
                "name": "Location",
                "type": "text",
                "options": [],
                "value": "Over the rainbow"
            },
            {
                "id": "b547",
                "name": "Description",
                "type": "textarea",
                "options": [],
                "value": "A place of wonderful discovery"
            },
            {
                "id": "c12f",
                "name": "Assessment",
                "type": "select",
                "options": [
                    {
                        "id": "e5fd",
                        "name": "0"
                    },
                    {
                        "id": "e970",
                        "name": "1"
                    },
                    {
                        "id": "0ff4",
                        "name": "2"
                    },
                    {
                        "id": "2db3",
                        "name": "3"
                    },
                    {
                        "id": "241f",
                        "name": "4"
                    },
                    {
                        "id": "3f52",
                        "name": "5"
                    }
                ],
                "value": "241f"
            }
        ]
    }
}

眼尖的人会看到最后一个value对象“241f”的值也可以在options数组中针对其中一个id对象看到。当像这样嵌套时，我需要提取相应的值name，在本例中为“4”。

JSON 格式的信息位于表customfield字段textvalue中。它的数据类型是text但我将其强制为json。我最初在尝试在 WHERE 子句中应用条件时遇到数组设置错误，然后我阅读了有关使用 LATERAL 子查询的信息。它现在运行但返回所有选项，而不仅仅是匹配value.

恐怕我无法让 SQL Fiddle 工作来重现我的结果，但我非常感谢检查我的查询以查看是否可以发现问题。

with cte_custombundledfields as
     (
            select
                   textvalue
                 , cfname
                 , json_array_elements(textvalue::json -> 'baseGroupId'->'fields') ->> 'name'  as name
                 , json_array_elements(textvalue::json -> 'baseGroupId'->'fields') ->> 'value' as value
                 , json_array_elements(textvalue::json -> 'baseGroupId'->'fields') ->> 'type'  as type
            from
                   customfield
     )
   , cte_custombundledfieldsoptions as
     (
            select *
                 , json_array_elements(json_array_elements(textvalue::json -> 'baseGroupId'->'fields') -> 'options') ->> 'name' as value2
            from
                   cte_custombundledfields                                                   x
                 , LATERAL json_array_elements(x.textvalue::json -> 'baseGroupId'->'fields') y
                 , LATERAL json_array_elements(y -> 'options')                               z
            where
                   type    = 'select'
                   and z ->> 'id' = x.value
     )
select *
from
       cte_custombundledfieldsoptions

标签： sqljsonpostgresqlpostgresql-9.4

sql - 尝试构造 PostgreSQL 查询以从 JSON 中提取对象、数组、对象、数组、对象中的文本值

问题描述

解决方案

推荐阅读