在PostgreSQL jsonb中将对象转换为大小为1的数组,并将具有嵌套数组的json转换为行

6l7fqoea  于 2022-11-04  发布在  PostgreSQL
关注(0)|答案(2)|浏览(145)

我有一个两部分的问题
我们有一个包含jsonb列的PostgreSQL表。jsonb中的值是有效的json,但是对于某些行,节点将以数组的形式出现,而对于其他行,节点将以对象的形式出现。
例如,我们接收到的json可能是这样的(* node 4 * I只是一个对象)

"node1": {
    "node2": {
        "node3": {
            "node4": {
                "attr1": "7d181b05-9c9b-4759-9368-aa7a38b0dc69",
                "attr2": "S1",
                "UserID": "WebServices",
                "attr3": "S&P 500*",
                "attr4": "EI",
                "attr5": "0"
            }
        }
    }
}

或者如下所示(* node 4 * 是一个数组)

"node1": {
    "node2": {
        "node3": {
            "node4": [
                {
                    "attr1": "7d181b05-9c9b-4759-9368-aa7a38b0dc69",
                    "attr2": "S1",
                    "UserID": "WebServices",
                    "attr3": "S&P 500*",
                    "attr4": "EI",
                    "attr5": "0"
                },
                {
                    "attr1": "7d181b05-9c9b-4759-9368-aa7a38b0dc69",
                    "attr2": "S1",
                    "UserID": "WebServices",
                    "attr3": "S&P 500*",
                    "attr4": "EI",
                    "attr5": "0"
                }
            ]
        }
    }
}

我需要写一个jsonpath查询来提取,例如,attr 1,对于每个包含这个json的PostgreSQL行。我希望有一个jsonpath查询,不管节点是对象还是数组,它总是有效的。所以,我想使用如下的路径,假设,如果它是数组,它将返回数组中所有索引的值。

jsonb_path_query(payload, '$.node1.node2.node3.node4[*].attr1')#>> '{}' AS "ATTR1"

我想避免检查类型是数组还是对象,然后对每个类型运行单独的查询并执行联合。
这可能吗?
一个与上面相关的子问题-因为我需要输出为文本,没有引号,我在某处看到使用#>> '{}'-所以我尝试了,它是工作的,但有人能解释一下,它是如何工作的吗?
问题的第二部分是-传入的json可以有多组嵌套数组,并且json和节点的数量非常大。因此,我想做的另一部分是将json扁平化为多行。我找到的示例是必须标识每一层,然后使用交叉连接或取消嵌套。我希望有一种方法可以扁平化数组节点,包括所有父对象的信息,而不知道它的父对象是数组还是简单对象。这也可能吗?

更新

我试着看文档,试着理解#>> '{}'结构,然后我意识到'{}'是#〉〉运算符的右手操作数,它取一个路径,在我的例子中,路径是当前属性值,因此{}。查看具有非空单属性路径的示例帮助我意识到这一点。
谢谢你

n3schb8v

n3schb8v1#

您可以在JSON路径表达式中使用“递归术语”:

select t.some_column,
       p.attr1
from the_table t
  cross join jsonb_path_query(payload, 'strict $.**.attr1') as p(attr1)

请注意,strict修饰符是必需的,否则,每个值将被多次返回。
这将为JSON结构的任何级别中的每个关键字attr1返回一行。
对于给定的示例数据,这将返回:

attr1                                 
--------------------------------------
"7d181b05-9c9b-4759-9368-aa7a38b0dc69"
"7d181b05-9c9b-4759-9368-aa7a38b0dc69"
"7d181b05-9c9b-4759-9368-aa7a38b0dc69"
a7qyws3x

a7qyws3x2#

  • “我想避免检查类型是数组还是对象,然后对每个类型运行一个单独的查询,并进行联合。可能吗?"*

是的,当node 4是jsonb对象或jsonb数组时,jsonpath查询在这两种情况下都能正常工作,因为jsonpath通配符数组访问器[*]也可以处理lax mode中的jsonb对象,这是默认的行为(但在strict mode中不能,请参阅手册)。请参阅dbfiddle中的测试结果。

  • “我看到要使用#〉〉'{}' -所以我试了一下,它是有效的,但是有人能解释一下,它是如何工作的吗?"*

jsonb_path_query函数的输出是jsonb类型,当结果是jsonb字符串时,则在查询结果中自动显示为带有双引号"。运算符#>>将输出转换为文本类型,在查询结果中不显示",并且关联的文本数组'{}'正好指向传递的jsonb数据。

  • ”传入的json可以有多组嵌套数组,并且json和节点的数量非常大。因此,我想做的另一部分是将json扁平化为多行”*

您可以使用递归通配符成员访问器.**引用the answer of a_horse_with_no_name

相关问题