在列中查询json数组

Question

我有一个SQL表，其中一列作为jsonb数据类型。下面是一个json条目:

{
"size": -1,
"regions": [
{
"shape_attributes": {
"name": "polygon",
"X": [
2703,
2801,
2884
]
},
"region_attributes": {
"Material Type": "wood",
"Color": "red"
}
},
{
"shape_attributes": {
"name": "polygon",
"X": [
2397,
2504,
2767
]
},
"region_attributes": {
"Material Type": "metal",
"Color": "blue"
}
}
],
"filename": "filenam_1"
}

我正在使用PostgresSQL。给定一个search_string，我如何使用SQL为这两种情况选择行-

1. 键是已知的
2. 键是未知的，即字符串在json

我试过了

select * 
from TABLE_Name
WHERE ‘wood’ IN ( SELECT value FROM OPENJSON(COL_NAME,'$.Material Type'))  
---
Error occurred during SQL query execution
Reason:
SQL Error [42883]: ERROR: function openjson(jsonb, unknown) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

SELECT * 
FROM  TABLE_Name
CROSS APPLY OPENJSON(COL_NAME,'$.Material Type')
WHERE value ='wood'
---
Error occurred during SQL query execution
Reason:
SQL Error [42601]: ERROR: syntax error at or near "APPLY"

Answer 1

要查找具有已知键的键/值对，可以使用几种不同的方法，使用包含操作符是其中之一:

select *
from table_name
where the_jsonb_column @> '{"regions": [{"region_attributes": {"Material Type": "wood"}}]}'

上述openjson函数的等价物(来自SQL Server)将是jsonb_each()，但(就像openjson)只会扩展顶级键/值对。它不会递归地这样做。

如果你至少知道键在regions数组的某个地方，你可以使用JSON/Path表达式迭代所有元素(递归):

select *
from table_name
where (t.the_jsonb_column -> 'regions') @@ '$[*].** == "wood"'

Answer 2

我觉得你做的事根本不可能，除非我不知道。您可以使用编程语言，如Python或c#，并在程序中执行SQL查询。这就容易多了。