Yep, that does it. Apparently I was overcomplicating things. Thanks!
On Fri, Mar 16, 2018 at 8:12 PM, Ryan Culpepper <ry...@ccs.neu.edu> wrote:
> 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.
