Yep, that does it. Apparently I was overcomplicating things. Thanks!
On Fri, Mar 16, 2018 at 8:12 PM, Ryan Culpepper <[email protected]> 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 [email protected].
For more options, visit https://groups.google.com/d/optout.
