Querying using json path operators in PostgreSQL

[jan0sch]

Hi,

I'm currently banging my head against a query that is using json path operators and supposed to work like this:

SELECT TRUE FROM table WHERE id = MYID AND json @? '$.key == MYVALUE'

However I get different kind of errors depending on how I write the query in Scala/Doobie:

1. SELECT TRUE FROM table WHERE id = $id AND json @? '$$.key == $value'
   compiles but gives: syntax error at or near "'$.key == ?'"
2. SELECT TRUE FROM table WHERE id = $id AND json @? $$.key == $value
   compiles but gives: No value given for parameter 3.
3. SELECT TRUE FROM table WHERE id = $id AND json @?? '$$.key == $value'
   compiles but gives: The column 2 index is invalid. Columns: 1.
4. SELECT TRUE FROM table WHERE id = $id AND json @?? $$.key == $value
   compiles but gives: syntax error near "$"

Breaking it up like:

val prefix      = fr"""SELECT TRUE FROM table"""
val filterId    = fr"""id = $id"""
val filterValue = fr"""json @? '$$.key == """ ++ fr"""$value""" ++ fr"""'"""
val sqlQuery    = prefix ++ whereAnd(filterId, filterValue)

compiles but gives: syntax error at or near "'$.key == ? '"

Any ideas?

Kind regards,

[jatcwang]

A bit of implementation background, Doobie merely turns any interpolated values in fragments into ?.
So what Postgres sees from your first attempt is this SQL: SELECT TRUE FROM table WHERE id = ? AND json @? '$.key == ?'. Notice that the second ? is inside a SQL string literal, so Postgres doesn't treat that as a "hole" to plug in the parameter.

In this case since your JSON path is static, I think it might be simpler to do ... AND json->>'key' = ??

So your doobie fragment will look like fr"SELECT TRUE FROM table WHERE id = $id AND json->>'key' = $value"

[jan0sch]

Thanks, that gave me pointers.

The snippet works fine in a SQL console: SELECT TRUE FROM table WHERE id = $id AND json->>'key' = $value

However Doobie always returns an empty result set on that query. Double checked that data is there, logged query used by Doobie, put it into console with values and it works. Pretty fascinating. :-|

Best to call it day because I'm tempted to either filter on a stream of all entries or simply write a stored procedure for that. ;-)

[jatcwang]

Perhaps double check the type of json->>'key' and $value and make sure they match

[jan0sch]

AFAIK ->> extracts the value as text which is okay and the comparison $value is an opaque type that maps to a String beneath. I'll check with using an explicit String type as $value.

[jan0sch]

Same behaviour/result (i.e. empty result) when explicitly converting $value to a String.
That stored procedure sounds like a great idea meanwhile. ;-)

[jan0sch]

For posterity, I created a function (in the postgresql database) that takes the id and value as parameters and does the actual query. The function is called from Doobie and it works as expected.