JSON value using placeholder substitution causes ProgrammingError

[jimiolaniyan]

When I run the following query:

  select id from response, jsonb_array_elements(data) as elem
  where
      elem ->> 0 = '1,%s' 
      and elem -> 1 @> '%s' 
      and project_id = %s 

I get the error:

sqlalchemy.exc.ProgrammingError: (pg8000.dbapi.ProgrammingError) {'S': 'ERROR', 'V': 'ERROR', 'C': '22P02', 'M': 'invalid input syntax for type json', 'D': 'Token "%" is invalid.', 'W': 'JSON data, line 1: %...', 'P': '216', 'F': 'jsonfuncs.c', 'L': '631', 'R': 'json_ereport_error'}
[SQL: 
      select id from response, jsonb_array_elements(data) as elem
      where
          elem ->> 0 = '1,%s' 
          and elem -> 1 @> '%s' 
          and project_id = %s 
]
[parameters: (4, 3333, 99)]
(Background on this error at: https://sqlalche.me/e/20/f405)

The error seems to come from the substitution happening in where clause elem ->> 0 = '1,%s' and elem -> 1 @> '%s' as the query works when I remove that part.

Is there a way to check if a JSON element is a certain value using placeholder substitution?

[tlocke]

Hi @jimiolaniyan, thanks for the bug report, I'll have a closer look later on, but in the meantime I wonder if you could create a https://stackoverflow.com/help/minimal-reproducible-example

It seems like you're using SQLAlchemy, so a question is: can you reproduce the bug using pg8000 on its own? That type of thing gets us a long way on the road to understanding the bug. Thanks!

[tlocke]

To answer one of your questions:

Is there a way to check if a JSON element is a certain value using placeholder substitution?

the answer is yes. Here's an example:

import pg8000.dbapi

with pg8000.dbapi.connect("postgres") as con:
    cur = con.cursor()
    cur.execute(""" SELECT '{"a":1, "b":2}'::jsonb @> %s """, ({"b": 2},))
    for row in cur.fetchall():
        print(row)

which gives the output:

[True]

I think the problem you're having is that you're quoting the %s.

[jimiolaniyan]

@tlocke Thanks for the reply. Removing the quotes works as suggested. This is quite different from how psycopg2 and other libraries work as the query will fail with the error:

ERROR: operator does not exist: jsonb = integer

Thanks for the suggestion.

[tlocke]

Removing the quotes works as suggested. This is quite different from how psycopg2 and other libraries work as the query will fail

Yes, there are two ways of sending a query to PostgreSQL, the simple or extended method. Many clients use the simple method where parameters are substituted in to the query to result in a string that's sent to the server. Other clients like pg8000 use the extended method for parameterised queries, where the query string with placeholders is sent to the server, and the parameters are sent separately. Unfortunately it means that in practice the query string may have to be modified when going from one client to another.

[tlocke]

Also see the section Theory of Operation for a bit more info on how pg8000 works.

[jimiolaniyan]

Alright, this makes it clearer. Thanks for your help.