Maran
Reputation: 171
Snowflake - flatten multiple nested array values from json variant column
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]});
$$;
Upvotes: 1
Views: 5474
Answers (1)
Mike Walton
Reputation: 7369
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;
Upvotes: 4
Related Questions
- Json flattening in Snowflake - array, data object
- How to flatten nested Json dynamically on snowflake or javascript?
- Flatten variable length JSON in Snowflake
- Flatten and reconstruct JSON Snowflake
- I need to flatten JSON data in Snowflake Table1 table, in one JSON_DATA column that has an array
- How to explode several list values JSON within JSON with lateral flatten in Snowflake?
- Flatten nested JSON in snowflake
- Flatten multiple names arrays within variant json column in snowflake
- How do I flatten a nested json key/value pair into a single array of values?
- Lateral flatten array with multiple JSON objects in Snowflake