SQL for json array in column

user_4489082 0
Viewed 347

I have a SQL table with one of the column as jsonb datatype. Below is a json entry:

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

I am using PostgresSQL. Given a search_string, how can I use SQL to select rows for the two cases-

  1. Key is known
  2. Key is not known, i.e. string anywhere in json

I have tried this

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"
postgresql sql json
2 Answers

I think what you are doing isn't even possible at all, unless I don't know it. You could rather use a programming language, like Python or C# and execute the SQL Queries in the program. It is much more easier.

user_120108910

To find a key/value pair with a known key, you can use several different methods, using the contains operator is one of them:

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

The equivalent of the mentioned openjson function (from SQL Server) would be jsonb_each() but that (just like openjson) will only expand the top-level key/value pairs. It doesn't do this recursively.

If you at least know the key is somewhere in the regions array, you can use a JSON/Path expression that iterates over all elements (recursively):

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