Skip to main content

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.

Here is an example with adhoc query 

W (admin) > select dataa0] as id, makeJSON(dataa1]).customer as customer,  makeJSON(dataa1]).items.product as prodcut from PGReaderStream;
Processing - select dataa0] as id, makeJSON(dataa1]).customer as customer,  makeJSON(dataa1]).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


Terms & ConditionsCookie settings