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

Hi there - I am trying to create a materialized view to improve performance of a query in snowflake by splitting the json field in a table into structured columns. Since materialized views allow only single table in the definition, am not able to move forward. My JSON contains nested json values as shown in the below sample query. I have orders and each order will have corresponding items in the line items node within a json. Please let me know if there is a workaround to achieve this.

CREATE OR REPLACE MATERIALIZED VIEW Test    
AS
SELECT JSON:orderNumber::VARCHAR as order_number
FROM orderitem o,
TABLE(flatten(json:lineItems)) items
,items.value:id::VARCHAR as item_id

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