The table which needs to be converted to a json field.
ID, product, line_item, createdDate
123, valA, valB, '2019-02-02'
The JSON table would be like.
ID, json_column
123, { valA :
{valB : '2019-02-02'}}
Now, I'm not sure what kind of parse_json function can be used to create this column. When I use the column name, it errors out - 'Invalid Identifier'
Query used.
select ID, parse_json( {product : { line_item : createdDate }};
First of all, always try to provide a fully reproducible example
Here's one for your question based on what you wrote
create or replace table x(id int,
product varchar,
line_item varchar,
createdDate varchar)
as select * from values
(123,'valA','valB','2019-02-02');
select * from x;
-----+---------+-----------+-------------+
ID | PRODUCT | LINE_ITEM | CREATEDDATE |
-----+---------+-----------+-------------+
123 | valA | valB | 2019-02-02 |
-----+---------+-----------+-------------+
Now, to get the result you want you can use the OBJECT_CONSTRUCT function, like this:
select id, object_construct(product, object_construct(line_item, createddate)) from x;
-----+---------------------------------------------------------------------+
ID | OBJECT_CONSTRUCT(PRODUCT, OBJECT_CONSTRUCT(LINE_ITEM, CREATEDDATE)) |
-----+---------------------------------------------------------------------+
123 | { |
| "valA": { |
| "valB": "2019-02-02" |
| } |
| } |
-----+---------------------------------------------------------------------+
You might also need OBJECT_AGG if you want to group multiple line items together.
