Avoid parsing jsonb data

Questions / Help
,
1

I have a jsonb column in postgres that has some database-side code interacting with it. (So it has to remain jsonb.) My Elixir server on the other hand doesn’t interact with the contents of the column at all.

Because the json can be fairly large I’d like to avoid parsing json from the client and just treat it as an opaque value from the client passed straight through to the database. If possible, I’d like to avoid building a map when getting the column from the database too and just pass a string on to the client. (I don’t know if postgres’ wire protocol would permit that though, maybe it doesn’t give the json as a string but as some binary format that must be parsed?)

I’ve looked into creating a custom Ecto.Type, but it looks like the database responses get parsed to a map before hitting the load function. Returning a string from cast appears to store a string in the json column rather than the object inside the string.

Is there a way to make this work?

1 Like
2

did you try to set :json to nil here: Postgrex.Types — Postgrex v0.15.10

3

Added this bit to my code:

  Postgrex.Types.define(
    DServer.PostgrexTypes,
    Ecto.Adapters.Postgres.extensions(),
    [json: nil]
  )

along with the matching config. It gave me this error:

** (Postgrex.QueryError) type `jsonb` can not be handled by the types module DServer.PostgrexTypes, it must define a `:json` library in its options to support JSON types

Maybe I could define a Postgrex.Extension to handle the jsonb data? Looking through the docs doesn’t really make it clear whether an extension can override the default jsonb handling.

Only unfortunate bit about that is it doesn’t sound like I could have both a “pass-through” jsonb column and a regular :map jsonb column.

4

A quick workaround could be to specify the expected type in the select and let DB cast it. A query with such a select could be used as the base query for all reads.

# SELECT s0."jsonb_column"::varchar FROM ...
select(SomeSchema, [s], type(s.jsonb_column, :string))

You can also use different ecto schemas for reads and writes, one with field ... :map for writes to verify the structure before insert and one with field ... :string to cast jsonb as varchar when reading.

4 Likes