operator does not exist: json @> unknown

Question

I have the following table and I want the layout name whose userid='user-1' and gridname='RT'

| userid |                                                                              defaultdata |
|--------|------------------------------------------------------------------------------------------|
| user-1 | [{"gridname":"RT", "layoutname":"layout-1"},{"gridname":"RT2", "layoutname":"layout-2"}] |
| user-2 | [{"gridname":"RT", "layoutname":"layout-3"},{"gridname":"RT2", "layoutname":"layout-2"}] |

I have tried this so far.

 SELECT userid, obj.value->>'gridname' AS gridname
 FROM   col.userdefault t
 JOIN   lateral jsonb_array_elements(t.defaultdata::jsonb) obj(value) ON 
 obj.value->>'_gridname' = 'RT'
 WHERE  defaultdata @> '[{"_gridname":"RT"}]';

But not working and getting the below error:

ERROR:  operator does not exist: json @> unknown
LINE 4: WHERE  defaultdata @> '[{"_gridname":"RT"}]::jsonb';
                           ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 198

Answer 1

The @> operator does not exist for json, only for jsonb.

You can get rid of the immediate error with an explicit type cast, like the error message suggests:

WHERE defaultdata::jsonb @> '...'

Looking farther though, I wonder if json is the correct data type for you. For data that you want to manipulate inside the database efficiently, jsonb is better.

Answer 2

demo: db<>fiddle

To get the value of gridname you have to expand your array and then check every element of it:

SELECT
    userid,
    elems ->> 'layoutname' AS layoutname                  -- 3
FROM
    mytable,
    json_array_elements(defaultdata) elems                -- 1
WHERE userid = 'user-1' AND elems ->> 'gridname' = 'RT'   -- 2

1. json_array_elements() expands your JSON array into one row each element.
2. The expanded elements can be checked for their relevant values and can be used as filter
3. After that you are able to get your data

---

I played around with the @> comparator. I found out that your way was not bad (assuming you would have used type jsonb instead of json). @> can make the job:

SELECT 
    *
FROM mytable
WHERE defaultdata @> '[{"gridname":"RT"}]'

demo

This returns the whole array, not only the element containing this. Meaning if you would use it that way it return the array where any element contains this part.

To get the one element you need to expand it what you already did. Your only problem was that you did not use the expanded elements within your WHERE clause but the original data. Using the expanded elements would lead in this query:

SELECT 
    userid,
    elems ->> 'layoutname' AS layoutname
FROM mytable,
    jsonb_array_elements(defaultdata) elems
WHERE userid = 'user-1' AND elems @> '{"gridname":"RT"}'

demo

And this works fine!