我有一个两部分的问题
我们有一个包含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扁平化为多行。我找到的示例是必须标识每一层,然后使用交叉连接或取消嵌套。我希望有一种方法可以扁平化数组节点,包括所有父对象的信息,而不知道它的父对象是数组还是简单对象。这也可能吗?
更新
我试着看文档,试着理解#>> '{}'
结构,然后我意识到'{}'是#〉〉运算符的右手操作数,它取一个路径,在我的例子中,路径是当前属性值,因此{}。查看具有非空单属性路径的示例帮助我意识到这一点。
谢谢你
2条答案
按热度按时间n3schb8v1#
您可以在JSON路径表达式中使用“递归术语”:
请注意,
strict
修饰符是必需的,否则,每个值将被多次返回。这将为JSON结构的任何级别中的每个关键字
attr1
返回一行。对于给定的示例数据,这将返回:
a7qyws3x2#
是的,当node 4是jsonb对象或jsonb数组时,jsonpath查询在这两种情况下都能正常工作,因为jsonpath通配符数组访问器
[*]
也可以处理lax mode
中的jsonb对象,这是默认的行为(但在strict mode
中不能,请参阅手册)。请参阅dbfiddle中的测试结果。jsonb_path_query
函数的输出是jsonb
类型,当结果是jsonb字符串时,则在查询结果中自动显示为带有双引号"
。运算符#>>
将输出转换为文本类型,在查询结果中不显示"
,并且关联的文本数组'{}'
正好指向传递的jsonb数据。您可以使用递归通配符成员访问器
.**
引用the answer of a_horse_with_no_name