Ошибка оператор не существует json unknown

I am trying to run this line of code. It is making a sqlalchemy query that is case insensitive for the data.
precedent_query = and_(Db_obj.data.ilike(f"{{'collections': [{{'idType': {id_type}, 'externalId': {external_id}}}]}}"))

The docs in sqlalchemy I looked at didn’t help me understand what the jsonb ~~* unknown part of the error means, is it that it doesnt know what jsonb is? I have that imported. Or is it the ~~* that is unknown?

Any help or better solutions to a case insensitive version of this(precedent_query = and_(Db_obj.data.contains({'collections': [{'idType': id_type, 'externalId': external_id}]}))) would be greatly appreciated

My error is

Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 2463, in __call__
    return self.wsgi_app(environ, start_response)
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 2449, in wsgi_app
    response = self.handle_exception(e)
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1866, in handle_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 2446, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1951, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1820, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python3.7/site-packages/flask/_compat.py", line 39, in reraise
    raise value
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1949, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.7/site-packages/flask/app.py", line 1935, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/web/answers/api/answers/views.py", line 107, in answers
    error_message, error_code, response_payload = paginate_response(filters, data, ObjectGroup)
  File "/web/answers/api/helpers.py", line 108, in paginate_response
    count = data.count()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3578, in count
    return self.from_self(col).scalar()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3322, in scalar
    ret = self.one()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3292, in one
    ret = self.one_or_none()
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3261, in one_or_none
    ret = list(self)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3334, in __iter__
    return self._execute_and_instances(context)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3359, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 988, in execute
    return meth(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 287, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1107, in _execute_clauseelement
    distilled_params,
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1253, in _execute_context
    e, statement, parameters, cursor, context
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1473, in _handle_dbapi_exception
    util.raise_from_cause(sqlalchemy_exception, exc_info)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 398, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb, cause=cause)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 152, in reraise
    raise value.with_traceback(tb)
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1249, in _execute_context
    cursor, statement, parameters, context
  File "/usr/local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 552, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedFunction) operator does not exist: jsonb ~~* unknown
LINE 4: ..."idType": "Foo"}]}' OR db_obj.db_obj.data ILIKE '{''...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts. ```

\df *crypt in psql reveals the argument types of the pgcrypto encrypt and decrypt functions (as do the PgCrypto docs):

                                List of functions
 Schema |      Name       | Result data type |   Argument data types    |  Type  
--------+-----------------+------------------+--------------------------+--------
 ...
 public | decrypt         | bytea            | bytea, bytea, text       | normal
 public | encrypt         | bytea            | bytea, bytea, text       | normal
 ...

so both the encrypt and decrypt functions expect the key to be bytea. As per the error message, «you might need to add explicit type casts».

However, it works fine here on Pg 9.1, so I suspect there’s more to it than you’ve shown. Perhaps you have another function also named encrypt with three arguments?

Here’s how it works on a clean Pg 9.1:

regress=# create table demo(pw bytea);
CREATE TABLE
regress=# insert into demo(pw) values ( encrypt( 'data', 'key', 'aes') );
INSERT 0 1
regress=# select decrypt(pw, 'key', 'aes') FROM demo;
  decrypt   
------------
 \x64617461
(1 row)

regress=# select convert_from(decrypt(pw, 'key', 'aes'), 'utf-8') FROM demo;
 convert_from 
--------------
 data
(1 row)

Awooga! Awooga! Key exposure risk, extreme admin caution required!

BTW, please think carefully about whether PgCrypto is really the right choice. Keys in your queries can be revealed in pg_stat_activity and the system logs via log_statement or via crypto statements that fail with an error. IMO it’s frequently better to do crypto in the application.

Witness this session, with client_min_messages enabled so you can see what’d appear in the logs:

regress# SET client_min_messages = 'DEBUG'; SET log_statement = 'all'; 
regress=# select decrypt(pw, 'key', 'aes') from demo;
LOG:  statement: select decrypt(pw, 'key', 'aes') from demo;
LOG:  duration: 0.710 ms
  decrypt   
------------
 \x64617461
(1 row)

Whoops, key possibly exposed in the logs if log_min_messages is low enough. It’s now on the server’s storage, along with the encrypted data. Fail. Same issue without log_statement if an error occurs to cause the statement to get logged, or possibly if auto_explain is enabled.

Exposure via pg_stat_activity is also possible.. Open two sessions, and:

• S1: BEGIN;
• S1: LOCK TABLE demo;
• S2: select decrypt(pw, 'key', 'aes') from demo;
• S1: select * from pg_stat_activity where current_query ILIKE '%decrypt%' AND procpid <> pg_backend_pid();

Whoops! There goes the key again. It can be reproduced without the LOCK TABLE by an unprivileged attacker, it’s just harder to time it right. The attack via pg_stat_activity can be avoided by revoking access to pg_stat_activity from public, but it just goes to show that it might not be best to send your key to the DB unless you know your app is the only thing ever accessing it. Even then, I don’t like to.

If it’s passwords, should you store them at all?

Furthermore, if you’re storing passwords, don’t two-way encrypt them; if at all possible salt passwords then hash them and store the result. You usually don’t need to be able to recover the password cleartext, only confirm that the stored hash matches the password the user sends you to log in when it’s hashed with the same salt.

If it’s auth, let someone else do it for you

Even better, don’t store the password at all, authenticate against LDAP, SASL, Active Directory, an OAuth or OpenID provider, or some other external system that’s already designed and working.

Resources

• Secure Password authentication explained simply — CodeProject
• Wikipedia: Challenge-response authentication
• Wikipedia: Cryptographic hash function
• Wikipedia: Salt (cryptography)
• Wikipedia: Password

and lots more.

How about this:

SELECT COUNT(*)
FROM auth_user
WHERE (date_joined >= NOW() - INTERVAL '48 hours') AND
    (date_joined <= NOW() - INTERVAL '24 hours')

There’s also a BETWEEN syntax that might feel more natural:

SELECT COUNT(*)
FROM auth_user
WHERE date_joined BETWEEN
    NOW() - INTERVAL '48 hours' AND
    NOW() - INTERVAL '24 hours'

Here is the PostgreSQL docs page where BETWEEN is described.

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

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.

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!