5

In RavenDB 5.4+, I want to configure a SQL ETL task that offloads data to a PostgreSQL database, using Npgsql built-in factory for Raven. The issue I have is that I want the entire document (this) stored in a jsonb data column.

The question: how can I achieve storing the entire document, e.g. this, in the ETL transformation example below to Postgres?
My goal is not having to specify the fields/columns of object Foo manually.

Example Postgres table:

create table public.foo
(
    id   text,
    data jsonb
);

Example ETL transformation script, in RavenDB:

loadToFoo ({
    id: id(this),
    data: this // <-- the issue
});

Which results in an exception:

Npgsql.PostgresException (0x80004005): 42804: column "data" is of type jsonb but expression is of type text

I've tried the following alternatives:

data: JSON.parse(this)

Error: 
    System.ArgumentException: Expected string but got Symbol
---
data: JSON.stringify(this)

Error:
    Npgsql.PostgresException (0x80004005): 42804: column "data" is of type jsonb but expression is of type text

I'm also not sure if it's related to RavenDB's transform logic, or the solution should be thought of in the means of something from the Jint runtime, or in Postgres/Npgsql.

Juliën
  • 9,047
  • 7
  • 49
  • 80
  • In Npgsql, you insert jsonb data by giving it a string but telling it that the type is jsonb (typically by setting NpgsqlDbType.Jsonb on your NpgsqlParameter). See [these docs](https://www.npgsql.org/doc/basic-usage.html#parameter-types) for more info. – Shay Rojansky Dec 23 '22 at 19:16
  • Thanks @ShayRojansky - this is where the culprit lies; I don't know how to specify or define the type in the RavenDB ETL transformation script. – Juliën Dec 27 '22 at 09:50

1 Answers1

2

You need to tell RavenDB what is the type that you want. This is done by passing a value & type directly, like so:

loadToFoo ({
    id: id(this),
    data: {'Value:' this, 'Type': 'Jsonb'}
});
Ayende Rahien
  • 22,925
  • 1
  • 36
  • 41