JSON support (unexpected quotes)

[mesgar]

Trying to get data from JSON field in PostgreSQL.

my code:
db.Query("messages") .Select("message ->> 'deviceid' AS deviceid", "message ->> 'status' AS status") .Where("CAST ( message->> 'temperature' AS DECIMAL)", ">", 28).Get();

output query :
SELECT "message ->> 'deviceid'" AS "deviceid", "message ->> 'status'" AS "status" FROM "messages" WHERE "CAST ( message->> 'temperature'" AS "DECIMAL)" > 28

expected output :
SELECT message ->> 'deviceid' AS deviceid, message ->> 'status' AS status FROM "messages" WHERE CAST ( message->> 'temperature' AS DECIMAL) > 28

Not sure if it's an issue since JSON(NQSQL) is not supported. But it would be great if I could find a way to get rid of extra quotes.
Any suggestions are appreciated. thanks.

[ahmad-moussawi]

Currently, you can use the SelectRaw and WhereRaw methods instead, and use the ? as a placeholder for your parameters.

db.Query("messages")
  .SelectRaw("message ->> 'deviceid' AS deviceid", "message ->> 'status' AS status")
  .WhereRaw("CAST ( message->> 'temperature' AS DECIMAL) > ?", 28)
 .Get()

[mesgar]

Thanks. 👍

Your code caused an exception
SELECT message ->> 'deviceid' AS deviceid FROM "messages" WHERE CAST ( message->> 'temperature' AS DECIMAL) > 'message ->> 'status' AS status' Npgsql.PostgresException (0x80004005): 42883: operator does not exist: integer > text

I needed to make a few changes.

db.Query("messages")
  .SelectRaw("message ->> 'deviceid' AS deviceid, message ->> 'status' AS status")
  .WhereRaw("CAST ( message->> 'temperature' AS DECIMAL) > ?", 28)
 .Get()