SQL for json array in column

Question

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-

Key is known
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"

https://stackoverflow.com/questions/11472790/postgres-analogue-to-cross-apply-in-sql-server — jarlh, Aug 20 '21 at 11:50
Where [in the manual](https://www.postgresql.org/docs/current/static/functions-json.html) did you find the function `openjson()` — , Aug 20 '21 at 11:53
not in the manual, I found it here- https://stackoverflow.com/questions/47239225/how-to-search-sql-column-containing-json-array — pkj, Aug 20 '21 at 11:58
Why are you reading answers for a different DBMS rather than consulting the reference of the DBMS that you work with to find out which functions it supports? — , Aug 20 '21 at 12:35
because I do not know the difference, to me PostgreSQL is "SQL compliant" but how much, I do not know. Just got introduced to SQL. — pkj, Aug 20 '21 at 13:22

score 0 · Answer 1 · answered Aug 20 '21 at 11:54

0

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.

answered Aug 20 '21 at 11:54

lennart

88
6

apologies, just started SQL, but isn't my question very similar to https://stackoverflow.com/questions/47239225/how-to-search-sql-column-containing-json-array ? – pkj Aug 20 '21 at 12:07
See my answer which shows that your assumption isn't correct. – Aug 20 '21 at 12:13
you mean to say, if the key is not known then running a SQL query is not possible. – pkj Aug 20 '21 at 12:25

score 0 · Accepted Answer · 2021-08-20T13:02:01.947

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"'

and for unknown keys I'd explore json_each_text(json) and jsonb_each_text(jsonb) functions (https://www.postgresql.org/docs/9.5/functions-json.html) — Greg, Aug 20 '21 at 12:18

SQL for json array in column

2 Answers2