我有一个由两部分组成的问题
我们有一个带有jsonb列的PostgreSQL表。jsonb中的值是有效的json，但对于某些行，节点将以数组的形式出现，而对于其他行，它将以对象的形式出现。
例如，我们收到的json可能是这样的(node4我只是一个对象(
"node1": {
"node2": {
"node3": {
"node4": {
"attr1": "7d181b05-9c9b-4759-9368-aa7a38b0dc69",
"attr2": "S1",
"UserID": "WebServices",
"attr3": "S&P 500*",
"attr4": "EI",
"attr5": "0"
}
}
}
}
或者像这样(node4是一个数组(
"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查询来为每个包含这个json的PostgreSQL行提取，例如attr1。我希望只有一个jsonpath查询，无论节点是对象还是数组，它都能始终工作。所以，我想使用下面这样的路径，假设它是一个数组，它将返回该数组中所有索引的值。
jsonb_path_query(payload, '$.node1.node2.node3.node4[*].attr1')#>> '{}' AS "ATTR1"
我希望避免检查类型是在数组中还是在对象中，然后对每个类型运行单独的查询并进行并集。
有可能吗？
与上述内容相关的一个子问题-由于我需要不带引号的文本输出，并且我看到在某个地方使用#>> '{}'-所以我尝试了一下，它很有效，但有人能解释一下，它是如何工作的吗？
问题的第二部分是-传入的json可以有多组嵌套数组，json和节点的数量是巨大的。所以我想做的另一部分是将json扁平化为多行。我发现的例子是，必须识别每个级别，并使用交叉连接或unnest。我希望有一种方法可以使一个节点变平，它是一个数组，包括所有的父信息，而不知道它的父信息是数组还是简单对象。这也可能吗？
更新
我试图查看文档并理解#>> '{}'构造，然后我意识到"｛｝"是#>gt；运算符，它采用一个路径，在我的情况下，该路径是当前属性值，因此为{}。查看具有非空单属性路径的示例帮助我意识到了这一点。
谢谢
您可以使用；递归项"；在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"
"我希望避免检查类型是在数组中还是在对象中，然后对每个类型运行单独的查询并进行并集。有可能吗">
是的，当node4是jsonb对象或是jsonb数组时，您的jsonpath查询在这两种情况下都可以正常工作，因为jsonpath通配符数组访问器[*]也可以与lax mode中的jsonb对象一起工作，这是默认行为(但不在strict mode中，请参阅手册(。请参阅dbfiddle中的测试结果。
"；我看到使用#>gt；'"｛｝"-所以我试过了，它是有效的，但有人能解释一下，它是如何工作的吗">
jsonb_path_query函数的输出类型为jsonb，当结果是jsonb字符串时，它会自动在查询结果中显示为带有双引号的"。运算符#>>将输出转换为文本类型，该文本类型在查询结果中不显示"，并且相关联的文本数组'{}'正好指向传递的jsonb数据的根。
"；传入的json可以有多组嵌套数组，json和节点数量都很大。所以我想做的另一部分是将json扁平化为多行">
可以使用递归通配符成员访问器.**引用a_hors_with_no_name的答案