Write JSON field to postgres Database

[stuckeyam]

I'm working on a project to duplicate some of our production Postgres tables in a new BI environment so that reporting isn't interfering with our app. I thought this would be a pretty simple task, but I've run into some issues with JSON(B) fields. I don't like that there are JSON(B) fields in the production database, but that's not my call to make.

That being said, I need to be able to pull the tables from production and load them to 1:1 with the exact same structure in the new environment. The table structure is similar to this:

create table table1
(
   id integer not null,
   field1 text not null,
   field2 jsonb,
   field3 text
);

Alteryx is reading the JSON field2 input as string. How do I force the load into field2? Right now I'm getting: ERROR: invalid input syntax for type json;

Thanks for the help in advance!

[danilang]

Hi @stuckeyam 

If think we'll need some samples to see the format of the jsonb from field2.   Can you grab a few rows of the data, write them to a .yxdb file and post them here.  

Thanks

Dan

[stuckeyam]

Hey Dan, I don't think the structure should matter. I just need to pull a json from postgres and load to postgres. Literally just duplicating a table in the production environment into our reporting DB.

That being said, the json captures the return from our inventory call, so it can vary pretty drastically. It can be as simple as {"a":1, "b":2} or as complex as:

{"lineItems": [{"lineItem": {"id": 167523, "sku": "xx-XXXX", "quantity": 1, "suppliedQuantity": 1}, "inventory": {"sku": "xx-XXXX", "vendorId": 30, "productId": 3007, "vendorSku": "XXXX", "quantityAvailable": 0, "vendorDistributionCenterId": 5497}}, {"lineItem": {"id": 167524, "sku": "xx-XXXX", "quantity": 1, "suppliedQuantity": 1}}], "fulfillmentType": "PICK_UP"}

[stuckeyam · Accepted solution]

So, this is an annoying work around, but here's what I came up with. Originally we wanted Alteryx to create a temp table in the Pre SQL statement, but we I got an error that it couldn't create temp tables. Instead I created a new schema called temp, where a duplicate table is created, but with text fields instead of JSONB.

create table temp.table1
(
   id integer not null,
   field1 text not null,
   field2 text,
   field3 text
);

Then we truncate and load to that temp table, using the Post SQL Statement field to load to the actual table.

INSERT INTO public.table1
(
   id,
   field1,
   field2,
   field3
)
SELECT id,
  field1,
  field2::jsonb,
  field3
FROM temp.table1
;

 All of this was very frustrating because if you have a long JSONB object, it could get chopped at 255 characters without you realizing. Here's how to change that setting if needed.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Input-tool-json-field-truncated-how-to...

[carrjc2]

Hi, this community post may help

https://community.alteryx.com/t5/Analytics-Blog/SlamData-Alteryx-A-Slam-Dunk-Analytics-Partnership/b...

The tool can read from Postgres (including any JSONB) and write back to postgres thru Alteryx using macro provided in the gallery.

Jeff