How to retrieve all child nodes from JSON file

Question

I have below JSON file, which is in the external stage, I'm trying to write a copy query into the table with the below query. But it's fetching a single record from the node "values" whereas I need to insert all child elements for the values node. I have loaded this file into a table with the variant datatype. The query I'm using:

select record:batchId batchId, record:results[0].pageInfo.numberOfPages NoofPages, record:results[0].pageInfo.pageNumber pageNo,
record:results[0].pageInfo.pageSize PgSz, record:results[0].requestId requestId,record:results[0].showPopup showPopup,
record:results[0].values[0][0].columnId columnId,record:results[0].values[0][0].value val
from lease;

{
    "batchId": "",
    "results": [
        {
            "pageInfo": {
                "numberOfPages": ,
                "pageNumber": ,
                "pageSize": 
            },
            "requestId": "",
            "showPopup": false,
            "values": [
                [
                    {
                        "columnId": ,
                        "value": ""
                    },
                    
                    {
                        "columnId": ,
                        "value": 
                    }
                ]
            ]
        }
    ]
}

Does this answer you question: https://stackoverflow.com/questions/32065604/get-all-child-nodes-from-json — Serge de Gosson de Varennes, Oct 27 '20 at 08:48
I'm trying to load the data from the external stage to the snowflake table. I have already loaded the file into one of the tables called lease in JSON format. Now I'm trying to bring it into structured and tabular format into another table — vikram kumar, Oct 27 '20 at 10:57
Please can you provide the table structure and example data that you want to see in the output? Thanks — NickW, Oct 27 '20 at 11:14
Desired output: BATCHID,NOOFPAGES,PAGENO,PGSZ,REQUESTID,SHOWPOPUP,COlMID,VAL a,1,1,100000,a,FALSE,4567,2020-10-09T07:24:29.000Z — vikram kumar, Oct 27 '20 at 11:23

score 0 · Accepted Answer · answered Oct 27 '20 at 16:22

you need to use the LATERAL FLATTEN functions, something like this:

I created this table:

create table json_test (seq_no integer, json_text variant);

and then populated it with this JSON string:

insert into json_test(seq_no, json_text)
select 1, parse_json($${
    "batchId": "a",
    "results": [
        {
            "pageInfo": {
                "numberOfPages": "1",
                "pageNumber": "1",
                "pageSize": "100000"
            },
            "requestId": "a",
            "showPopup": false,
            "values": [
                [
                    {
                        "columnId": "4567",
                        "value": "2020-10-09T07:24:29.000Z"
                    },
                    
                    {
                        "columnId": "4568",
                        "value": "2020-10-10T10:24:29.000Z"
                    }
                ]
            ]
        }
    ]
}$$);

Then the following query:

select 
json_text:batchId batchId
,json_text:results[0].pageInfo.numberOfPages numberOfPages
,json_text:results[0].pageInfo.pageNumber pageNumber
,json_text:results[0].pageInfo.pageSize pageSize
,json_text:results[0].requestId requestId
,json_text:results[0].showPopup showPopup
,f.value:columnId columnId
,f.value:value value
from json_test t
,lateral flatten(input => t.json_text:results[0]:values[0]) f;

gives these results - which I think is roughly what you are looking for:

BATCHID    NUMBEROFPAGES    PAGENUMBER    PAGESIZE    REQUESTID    SHOWPOPUP    COLUMNID    VALUE
"a"        "1"              "1"           "100000"    "a"          false        "4567"      "2020-10-09T07:24:29.000Z"
"a"        "1"              "1"           "100000"    "a"          false        "4568"      "2020-10-10T10:24:29.000Z"

It fits my requirement, would mind explaining how "table flatten and lateral flatten" works. I tried going through snowflake documentation, It didn't help me to understand clearly — vikram kumar, Oct 28 '20 at 04:53
Hi - if you think my answer has helped please can you tick it? I'm absolutely not an expert on querying JSON but basically LATERAL is a type of join and FLATTEN, as it says, flattens out data i.e. produces multiple rows from a single row. So you are performing a cartesian join of your single row of data to the multiple rows produced by the FLATTEN function. The basic syntax is that the input needs to reference the node in your JSON that has the multiple child nodes that you want to flatten. This link may help: https://community.snowflake.com/s/article/How-To-Lateral-Join-Tutorial. — NickW, Oct 28 '20 at 10:27

How to retrieve all child nodes from JSON file

1 Answers1