How to create a materialized view with a view definition of flattening nested json

404

nekkanti009

posted on 26 Dec 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Post Comment

nekkanti00927-Dec-19

Since there are more than one items in the items node, based on my research in snowflake we need to use flatten syntax .. looking for options other than flatten to recursively get the item info in select and use that logic in materialized view. Any inputs/suggestions will be really helpful. Thanks in advance.

ReplyMore..ReportDelete

nVector27-Dec-19

I haven't done this. But i searched for this issue and found this

select * from table(flatten(input => parse_json('{"a":1, "b":[77,88]}'), path => 'b')) f;

+-----+------+------+-------+-------+-------+
| SEQ |  KEY | PATH | INDEX | VALUE | THIS  |
|-----+------+------+-------+-------+-------|
|   1 | NULL | b[0] |     0 |    77 | [     |
|     |      |      |       |       |   77, |
|     |      |      |       |       |   88  |
|     |      |      |       |       | ]     |
|   1 | NULL | b[1] |     1 |    88 | [     |
|     |      |      |       |       |   77, |
|     |      |      |       |       |   88  |
|     |      |      |       |       | ]     |
+-----+------+------+-------+-------+-------+

In your case you can mention the path as orders, and it should convert the line items to separate rows.

Let us know if it worked

ReplyMore..ReportDelete

nekkanti00927-Dec-19

Thanks for the response npack. If i replace "a":1, "b":[77,88]} with my json string it is splitting the nested json node into multiple rows based on the number of items. Imagine you have a table and the JSON column name is "JSON" that has order details and nested lineitems. Could you please let me know how i should change the above query?

I tried below 2 options and both didn't work:

select * from table(flatten(input => parse_json(orders.JSON), path => 'lineitems')) f;

select * from orders(flatten(input => parse_json(JSON), path => 'lineitems')) f;

ReplyMore..ReportDelete

nekkanti00927-Dec-19

I actually modified the query as shown below and it worked but only for one order. If i remove the where clause inside parse_json it is failing.

select *

from table(flatten(input => parse_json(select JSON from OrderItem where JSON:orderNumber = 'XXXX-XX-XXX'), path => 'lineItems')) f ;

ReplyMore..ReportDelete