How I'm supposed to query data from columns with type `_jsonb` or `_json` in PostgreSQL?

Help & Support
1

I’m using GitHub - will/crystal-pg: a postgres driver for crystal driver to use PostgreSQL and I have a really annoying error that I have no idea how to fix.

I have a column called watched which is a just an array of jsonb (_jsonb) and I’m trying to retrieve the data from that Array.

For example. I have this:

require "db"
require "pg"

PG_DB = DB.open "postgres://asd:asd@/asd"

request = <<-SQL
    select watched
    from users
    where email = $1
  SQL

asd = PG_DB.query_all(request, "user", as:JSON::PullParser)

pp asd

It returns this on compilation:

Unhandled exception: In PG::ResultSet#read the column watched returned a Array(PG::StringArray) but a JSON::PullParser was expected. (DB::ColumnTypeMismatchError)

If I do this:

require "db"
require "pg"

PG_DB = DB.open "postgres://asd:asd@/asd"

request = <<-SQL
    select watched
    from users
    where email = $1
  SQL

asd = PG_DB.query_all(request, "user", as:Array(JSON::PullParser))

pp asd

I gives me:

Showing last frame. Use --error-trace for full trace.

In lib/pg/src/pg/decoders/array_decoder.cr:72:31

 72 | decoder = array_decoder(T)
                              ^
Error: expected argument #1 to 'PG::Decoders.array_decoder' to be (Bool | Nil).class, (Char | Nil).class, (Float32 | Nil).class, (Float64 | Nil).class, (Int16 | Nil).class, (Int32 | Nil).class, (Int64 | Nil).class, (PG::Numeric | Nil).class, (String | Nil).class, (Time | Nil).class, (UUID | Nil).class, Array(T).class, Bool.class, Char.class, Float32.class, Float64.class, Int16.class, Int32.class, Int64.class, PG::Numeric.class, String.class, Time.class or UUID.class, not JSON::PullParser.class

How I can make this work? Here is a related issue: Error: can't cast to JSON::Any · Issue #263 · will/crystal-pg · GitHub

2

Have you tried JSON::Any? You for sure do not want to be using JSON::PullParser…

3

Yes, if I use JSON::Any, it gives the exact same error, that is because of this pull request that was merged: Json columns are read as JSON::PullParser instead of JSON::Any by matthewmcgarvey · Pull Request #232 · will/crystal-pg · GitHub

I added a method specifically for still supporting calling result_set.read(JSON::Any) with hopes that will cause the least amount of breakage, because this is definitely a breaking change.

1 Like
5

The Postgres binary wire protocol distinguishes between TEXT/VARCHAR and JSONB/JSON columns. When the pg shard receives it, it is told what oid to use to decode it. The pg shard decodes JSONB and JSON columns differently than it does TEXT because it’s a different data type.

You have two options:

  1. if you want the string, you can use SELECT watched::text
  2. if you want to avoid the intermediate string allocation (the reason it gives you a JSON::PullParser), you need to use query_each so you can parse the JSON from each row as it comes through
watched = [] of Array(String)
PG_DB.query_each request, "user" do |rs|
  watched << Array(String).new(rs.read(JSON::PullParser))
end

Replace Array(String) with the data structure that watched contains.

You can’t use query_all because the JSON::PullParser parses directly from the DB socket so, before query_all returns, all of the IO objects underlying the JSON::PullParsers (it uses an IO::Sized under the hood) are consumed and you can’t parse anything with them.