Solved

How do we flatten JSON in Striim?

  • 21 September 2022
  • 1 reply
  • 99 views

Badge
  • New Participant
  • 2 replies

I have a JSON Field in my PostgreSQL database with this format

 

I want to take all the fields in this flat JSON structure and turn each of them into fields in a typed stream. How would I go about doing that? I tried using the ‘makeJSON’ function on the JSON string column but I’m unable to run JSONNode function on it. 

 

Running the above query results in a ‘CRASH, cannot map’ exception.

icon

Best answer by Sid 21 September 2022, 04:45

View original

1 reply

Badge +1

Here is an example with adhoc query 

W (admin) > select data[0] as id, makeJSON(data[1]).customer as customer,  makeJSON(data[1]).items.product as prodcut from PGReaderStream;
Processing - select data[0] as id, makeJSON(data[1]).customer as customer, makeJSON(data[1]).items.product as prodcut from PGReaderStream
[
id = 7
customer = "John Doe"
prodcut = "Beer"
]

Here is DDL and DML used

CREATE TABLE orders (
id serial NOT NULL PRIMARY KEY,
info json NOT NULL
);

INSERT INTO orders (info)
VALUES('{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}');

 

Reply