尝试构造PostgreSQL查询以从JSON中提取对象，数组，对象，数组，对象中的文本值 [英] Trying to construct PostgreSQL Query to extract from JSON a text value in an object, in an array, in an object, in an array, in an object

问题描述

我正在构造PostgreSQL系统和SQL Server系统之间的接口，并试图拉平 JSON数据的结构以简化此过程。我在SQL Server方面经验丰富，但是对PostgreSQL和JSON还是陌生的。

I am constructing an interface between a PostgreSQL system and a SQL Server system and am attempting to "flatten" the structure of the JSON data to facilitate this. I'm very experienced in SQL Server but I'm new to both PostgreSQL and JSON.

JSON基本上包含两种结构类型：类型文本或文本区域，其中我想要的值位于名为 value 的对象中（以下前两种情况），以及类型选择，其中值对象指向 id 下层选项数组中的对象（下面的第三种情况）。

The JSON contains essentially two types of structure: those of type "text" or "textarea" where the value I want is in an object named value (the first two cases below) and those of type "select" where the value object points to an id object in a lower-level options array (the third case below).

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

那些敏锐的眼睛会看到最后一个值的值对象 241f也可以在 id 对象之一的选项数组中看到。当这样嵌套时，我需要提取相应的 name 的值，在这种情况下为 4。

Those with a sharp eye will see that the value of the last value object "241f" can also be seen within the options array against one of the id objects. When nested like this I need to extract the value of the corresponding name, in this case "4".

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

The JSON-formatted information is in table customfield field textvalue. It's datatype is text but I'm coercing it to json. I was originally getting array set errors when trying to apply the criteria in a WHERE clause and then I read about using a LATERAL subquery instead. It now runs but returns all the options, not just the one matching the value.

恐怕我无法获得SQL Fiddle来重现我的结果，但我非常希望检查一下我的查询以查看是否可以发现问题。

I'm afraid I couldn't get an SQL Fiddle working to reproduce my results, but I would really appreciate an examination of my query to see if the problem can be spotted.

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

推荐答案

我发布了对该问题的简化版本， Bergi 。

I posted a much-simplified rewrite of this question which was answered by Bergi.

如何根据PostgreSQL中JSON中的另一个字符串从JSON查询字符串？

这篇关于尝试构造PostgreSQL查询以从JSON中提取对象，数组，对象，数组，对象中的文本值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！