26 Dec 19 · nekkanti009 · #general ·   Bookmark   ×

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




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.

npack103d

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

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;

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 ;


Community Software by Hittly
Copied