Snowflake - flatten multiple nested array values from json variant column

Question

I have a JSON data from which I wanted to extract the values of key 'text' with delimited in single row. Any help to achieve the desired output is much appreciated.

Sample JSON data:

{
"expand": "schema,names",
"issues": [
        {
            "id": "123456",
            "key": "XYZ-123",
            "fields": {
                "customfield_10000": "abcd",
                "customfield_10001": 7,
                "customfield_10002": null,
                "description": {
                            "version": 1,
                            "type": "doc",
                            "content": [
                                {
                                    "type": "paragraph",
                                    "content": [
                                        {
                                            "type": "text",
                                            "text": "some text value 1"
                                        }
                                    ]
                                },
                                {
                                    "type": "paragraph",
                                    "content": [
                                        {
                                            "type": "text",
                                            "text": "some text value 2"
                                        },
                                        {
                                            "type": "text",
                                            "text": "some text value 3",
                                            "marks": [
                                                {
                                                    "type": "link",
                                                    "attrs": {
                                                        "href": "some ref"
                                                    }
                                                }
                                            ]
                                        },
                                        {
                                            "type": "text",
                                            "text": "some text value 4"
                                        }
                                    ]
                                },
                                {
                                    "type": "blockquote",
                                    "content": [
                                        {
                                            "type": "paragraph",
                                            "content": [
                                                {
                                                    "type": "text",
                                                    "text": "some text value 5"
                                                }
                                            ]
                                        },
                                        {
                                            "type": "paragraph",
                                            "content": [
                                                {
                                                    "type": "inlineCard",
                                                    "attrs": {
                                                        "url": "some url"
                                                    }
                                                },
                                                {
                                                    "type": "text",
                                                    "text": "some text value 6"
                                                }
                                            ]
                                        }
                                    ]
                                },
                                {
                                    "type": "paragraph",
                                    "content": [
                                        {
                                            "type": "text",
                                            "text": "some text value 7"
                                        }
                                    ]
                                },
                                {
                                    "type": "paragraph",
                                    "content": [
                                        {
                                            "type": "text",
                                            "text": "some text value 8"
                                        },
                                        {
                                            "type": "text",
                                            "text": "some text value 9",
                                            "marks": [
                                                {
                                                    "type": "link",
                                                    "attrs": {
                                                        "href": "some link"
                                                    }
                                                }
                                            ]
                                        }
                                    ]
                                },
                                {
                                    "type": "paragraph",
                                    "content": [
                                        {
                                            "type": "text",
                                            "text": "some text value 10"
                                        }
                                    ]
                                },
                                {
                                    "type": "paragraph",
                                    "content": [
                                        {
                                            "type": "text",
                                            "text": "some text value 11"
                                        }
                                    ]
                                },
                                {
                                    "type": "paragraph",
                                    "content": [
                                        {
                                            "type": "text",
                                            "text": "some text value 12"
                                        }
                                    ]
                            }
                        ]
                    }
                }
            }
        ]
}

Desired output:

ISSUE_ID ISSUE_KEY CF_10000 CF_10001 CF_10002   DESCRIPTION
123456   XYZ-123   abcd     7        null       some text value 1|some text value 2|some text value 3.....

I'm using the below query to get the arrays values. However I wanted the key 'text' values from arrays to be populated as above desired format.

    select
       ISSUE.value:id::number as ISSUE_ID,
       ISSUE.value:key::varchar as ISSUE_KEY,
       ISSUE.value:fields.customfield_10000::varchar as CF_10000,
       ISSUE.value:fields.customfield_10001::number as CF_10001,
       ISSUE.value:fields.customfield_10002::varchar as CF_10002,
       ISSUE.value:fields.description.content::varchar as DESCRIPTION
    from
       VARIANT_TABLE,
       lateral flatten( input => payload_json:issues, outer => true) as ISSUE

I have an UDF created to extract JSON array object key value into string of array, but that doesn't helped me to get the desired output from above shared JSON as it has nested arrays inside objects.

create or replace function UDF_ARRAY_OBJECT_TO_STRING_ARRAY(a array, b varchar)
returns array
language javascript
strict
comment = 'UDF to extract JSON array object key value into string of array. A refers to input array and B refers to extract which key from the array object'
as $$
return A.map(function(d) {return d[B]});
$$;

Mike Walton · Accepted Answer · 2020-08-20 00:53:49Z

You have a lot more arrays in there than you are handling in your lateral flattens. With a few more flattens and a listagg() function, you should get there with this. Note, you might need to group by the index, rather than the field values, depending on what you are trying to get to, but this gives the result you were looking for in your example:

WITH x AS (
SELECT parse_json('{
"expand": "schema,names",
"issues": [
        {
            "id": "123456",
            "key": "XYZ-123",
            "fields": {
                "customfield_10000": null,
                "customfield_10001": null,
                "customfield_10002": null,
                "description": {
                            "version": 1,
                            "type": "doc",
                            "content": [
                                {
                                    "type": "paragraph",
                                    "content": [
                                        {
                                            "type": "text",
                                            "text": "some text value 1"
                                        }
                                    ]
                                },
                                {
                                    "type": "paragraph",
                                    "content": [
                                        {
                                            "type": "text",
                                            "text": "some text value 2"
                                        },
                                        {
                                            "type": "text",
                                            "text": "some text value 3",
                                            "marks": [
                                                {
                                                    "type": "link",
                                                    "attrs": {
                                                        "href": "some ref"
                                                    }
                                                }
                                            ]
                                        },
                                        {
                                            "type": "text",
                                            "text": "some text value 4"
                                        }
                                    ]
                                },
                                {
                                    "type": "blockquote",
                                    "content": [
                                        {
                                            "type": "paragraph",
                                            "content": [
                                                {
                                                    "type": "text",
                                                    "text": "some text value 5"
                                                }
                                            ]
                                        },
                                        {
                                            "type": "paragraph",
                                            "content": [
                                                {
                                                    "type": "inlineCard",
                                                    "attrs": {
                                                        "url": "some url"
                                                    }
                                                },
                                                {
                                                    "type": "text",
                                                    "text": "some text value 6"
                                                }
                                            ]
                                        }
                                    ]
                                },
                                {
                                    "type": "paragraph",
                                    "content": [
                                        {
                                            "type": "text",
                                            "text": "some text value 7"
                                        }
                                    ]
                                },
                                {
                                    "type": "paragraph",
                                    "content": [
                                        {
                                            "type": "text",
                                            "text": "some text value 8"
                                        },
                                        {
                                            "type": "text",
                                            "text": "some text value 9",
                                            "marks": [
                                                {
                                                    "type": "link",
                                                    "attrs": {
                                                        "href": "some link"
                                                    }
                                                }
                                            ]
                                        }
                                    ]
                                },
                                {
                                    "type": "paragraph",
                                    "content": [
                                        {
                                            "type": "text",
                                            "text": "some text value 10"
                                        }
                                    ]
                                },
                                {
                                    "type": "paragraph",
                                    "content": [
                                        {
                                            "type": "text",
                                            "text": "some text value 11"
                                        }
                                    ]
                                },
                                {
                                    "type": "paragraph",
                                    "content": [
                                        {
                                            "type": "text",
                                            "text": "some text value 12"
                                        }
                                    ]
                            }
                        ]
                    }
                }
            }
        ]
}') as payload_json)
    select
       issue.value:id::number as ISSUE_ID,
       issue.value:key::varchar as ISSUE_KEY,
   ISSUE.value:fields.customfield_10000::varchar as CF_10000,
   ISSUE.value:fields.customfield_10001::number as CF_10001,
   ISSUE.value:fields.customfield_10002::varchar as CF_10002,
       listagg(content2.value:text::varchar,'|') as description
    from
       x,
       lateral flatten( input => x.payload_json:issues, outer => true) as issue,
       lateral flatten( input => issue.value:fields:description:content, outer => true) as content,
       lateral flatten( input => content.value:content, outer => true) as content2
    group by 1,2,3,4,5;

Great thanks @Mike Walton... this works fine... However, If I wanted to select customfield_10001(not an array) value as well its not working as it is neither an aggregate nor in the group by clause. Can you please help me with this..
@Manimaran: it looks like you are changing your requirements here... the answer here appears to meet all of your previous specifications. If you want to expand/alter your requirements, you should provide more clarity on those requirements in your post.
@Manimaran - you'd just need to add them to the group by clause. I've updated my answer to show. You could also do the listagg() function in a sub-select and then join it back to the original values on the record index. Either would work.

Collectives™ on Stack Overflow

Snowflake - flatten multiple nested array values from json variant column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related