Not passing paramater value with @> PostgreSQL operator

[michael-wolfenden]

Type

What kind of issue is this?

[X] Bug report.
[ ] Feature request.

Given the following

CREATE TABLE books (  
  book_id serial NOT NULL,
  data jsonb
);

INSERT INTO books VALUES (1, '{"title": "Sleeping Beauties"}');

I can use the @> operator to query books based on title

SELECT data->'title' FROM books WHERE data @> '{ "title": "Sleeping Beauties" }';

When reproducing this query using Insights

NpgsqlLogManager.Provider = new ConsoleLoggingProvider(minLevel: NpgsqlLogLevel.Debug);
NpgsqlLogManager.IsParameterLoggingEnabled = true;

PostgreSQLInsightDbProvider.RegisterProvider();

using (var connection = new NpgsqlConnection("....."))
{
    connection.Open();
    connection.QuerySql<string>(
        "SELECT data->'title' FROM books WHERE data @> '{ \"title\": \"@title\" }';",
        new { title = "Sleeping Beauties" }
    )
}

It returns zero results as it doesn't appear to substitute the @title variable. The NpgsqlLogManager output is:

Executing statement(s):
  SELECT data->'title' FROM books WHERE data @> '{"title": "@title" }'
Parameters:
  $1: Sleeping Beauties

[jonwagner]

This seems to indicate that the parameter is making it to postgres

Executing statement(s):
  SELECT data->'title' FROM books WHERE data @> '{"title": "@title" }'
Parameters:
  $1: Sleeping Beauties

Postgres is probably ignoring it because you have "@title" in quotes. Remove the quotes and it should treat it as a parameter.

[michael-wolfenden]

Removing the quotes also doesn't help

connection.QuerySql<string>(
     "SELECT data->'title' FROM books WHERE data @> '{ \"title\": @title }';",
    new { title = "Sleeping Beauties" }
);

results in

Executing statement(s):
  SELECT data->'title' FROM books WHERE data @> '{ "title": @title }'
Parameters:
  $1: Sleeping Beauties

PostgresException - 22P02: invalid input syntax for type json

Single quotes causes Postgres to treat it as a variable but then the syntax is invalid

connection.QuerySql<string>(
     "SELECT data->'title' FROM books WHERE data @> '{ \"title\": '@title' }';",
    new { title = "Sleeping Beauties" }
);

results in

Executing statement(s):
  SELECT data->'title' FROM books WHERE data @> '{ "title": '$1' }'
Parameters:
  $1: Sleeping Beauties

PostgresException - 42601: syntax error at or near "$1"

[jonwagner]

This post seems to show a different way to do it:

https://stackoverflow.com/questions/29484271/postgres-sending-parameter-to-jsonb-field-in-a-function

Have you tried:

SELECT data->'title' FROM books WHERE data->title @> @title;

Or something like that.

[michael-wolfenden]

That doesn't work either

Connection opened
Executing statement(s):
  SELECT data->'title' FROM books WHERE data->'title' @> $1
Parameters:
  $1: Sleeping Beauties

PostgresException - 42883: operator does not exist: jsonb @> text 

Which makes sense as the @> is the contains operator and its trying to check whether the jsonb title on the left (data->'title') contains the text on the right @title.

The only way I can this query working is to define the entire json object as a parameter

connection.QuerySql<string>(
    "SELECT data->'title' FROM books WHERE data @> @data::jsonb",
    new { data = "{ \"title\": \"Sleeping Beauties\" }" }
)

[jonwagner]

Right, and you would rather keep the json building in your stored proc so the c# code doesn't have to know about it.

If you hard-code the search parameter you would write this:

"SELECT Users.* FROM Users WHERE JsonData @> '{ \"Text\": \"MyText\" }'"

After the escape sequences are parsed, you get:

WHERE JsonData @> '{ "Text": "MyText" }'

Note that everything on the right-side of @> is a STRING, so we can't put any parameters inside it. We'll have to build that string up by concatenating the parameters:

"SELECT Users.* FROM Users WHERE JsonData @> ('{ \"Text\": \"' || @Text || '\" }')::jsonb"

After the escape sequences are parsed, you get:

WHERE JsonData @> ('{ "Text": "' || @Text || '" }')::jsonb

Assuming @Text is "MyText" that results in:

WHERE JsonData @> ('{ "Text": "MyText" }')::jsonb

Which is what we want.

For some reason PG doesn't like doing @> with a string on the right, so we cast it to jsonb and it gets converted into a jsonb template.

[jonwagner]

I think we figured this one out. Feel free to reopen if it doesn't work for you.