CODEWITHSUNDEEP
c#postgresqlravendbnpgsqljint
Juliën
Juliën

Reputation: 9532

RavenDB ETL to SQL, how to store entire document ('this') in jsonb column?

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.

Upvotes: 5

Views: 163

Answers (1)

Ayende Rahien
Ayende Rahien

Reputation: 22956

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'}
});

Upvotes: 3

Related Questions