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.
Best answer by Sid
View originalHere 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}}');
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.