On 03/16/2018 11:28 PM, David Storrs wrote:
I'm noticing that when I store jsexpr?s into PostgreSQL 10 I end up with
them as strings, not as actual JSONB data. I've read the docs and tried
every combination of typecasting / methods of writing that I can think
of but nothing ends up working. Can anyone point me to the right way?
Here's an example:
(require db)
(define c (dsn-connect 'pg))
(query c "create temporary table foo (i integer, j jsonb)")
(query-exec c "insert into foo (i,j) values ($1,$2)"
1 "hello")
(query-exec c "insert into foo (i,j) values ($1,$2)"
2 (hash 'a 1 'b 2))
(query-rows c "select * from foo")
;; => '(#(1 "hello") #(2 #hasheq((a . 1) (b . 2))))
(query-rows c "select i, j->'b' from foo")
;; => '(#(1 #<sql-null>) #(2 2))
It's a little trickier if you have json that's already in string form
and want it to be parsed on the PostgreSQL side:
(query-value c "select cast($1 as jsonb)" "{\"a\":1}") ;; WRONG
;; => "{\"a\":1}"
(query-value c "select cast($1::text as jsonb)" "{\"a\":1}") ;; RIGHT
;; => '#hasheq((a . 1))
(query-value c "select $1::text::json" "{\"a\":1}") ;; RIGHT
;; => '#hasheq((a . 1))
The inner "cast" (::) actually gets interpreted as a type ascription for
the parameter.
Finally, PostgreSQL treats (some?) type names as conversion functions,
so you can also do this:
(query-value c "select json($1::text)" "{\"a\":1}") ;; RIGHT
;; => '#hasheq((a . 1))
I hope that helps. Let me know if you have an example that isn't working
as you expect.
Ryan
--
You received this message because you are subscribed to the Google Groups "Racket
Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to racket-users+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.