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"

not in the manual, I found it here- stackoverflow.com/questions/47239225/… — pkj
– pkj, Commented Aug 20, 2021 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? — user330315
– user330315, Commented Aug 20, 2021 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
– pkj, Commented Aug 20, 2021 at 13:22

score 0 · Accepted Answer · 2021-08-20 13:02:01Z

0

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

edited Aug 20, 2021 at 13:02

answered Aug 20, 2021 at 12:12

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Greg Over a year ago

and for unknown keys I'd explore json_each_text(json) and jsonb_each_text(jsonb) functions (postgresql.org/docs/9.5/functions-json.html)

lennart · Accepted Answer · 2021-08-20 11:54:17Z

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, 2021 at 11:54

lennart

986 bronze badges

3 Comments

pkj Over a year ago

apologies, just started SQL, but isn't my question very similar to stackoverflow.com/questions/47239225/… ?

user330315 Over a year ago

See my answer which shows that your assumption isn't correct.

pkj Over a year ago

you mean to say, if the key is not known then running a SQL query is not possible.

Collectives™ on Stack Overflow

SQL for json array in column

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related