Loading JSON data into snowpipe

Question

we have below Valid JSON data which resides in S3 and we are trying load this data into snowflake table by snowpipe .

"Vendor": { "string": "ABC" }, "vmAddresses": [{ "Address": { "string": "addr1" }, "Category": { "string": "order" } ]

SELECT $1:Vendor.string::varchar,
$1:vmAddresses[0].Address.string,
object_keys($1:vmAddresses[0]),
object_pick($1:vmAddresses[0],'Address', 'Category')
FROM @S3://20210310194308.json

with OBJECT_KEYS we are able to get the keys but unable to get the corresponding value of it . the below format is what we are trying to get

{ "Address": "addr1", "Category": "order" }

Any help would be appreciated.

Answer 1

When I tried to validate your sample text trough parse_json and an online json formatter, both of them complained about invalid JSON. I corrected it, and run your SQL:

with json_data as (
select parse_json( '{ "Vendor": {"string": "ABC" }, "vmAddresses": [ { "Address": { "string": "addr1" }, "Category": { "string": "order" } } ] }' ) j)
select j:Vendor.string,
j:vmAddresses[0].Address.string,
object_keys(j:vmAddresses[0]),
object_pick(j:vmAddresses[0],'Address', 'Category')
from json_data;

And it worked as expected:

• j:vmAddresses[0].Address.string <-- returns "addr1"
• object_keys(j:vmAddresses[0]) <-- returns [ "Address", "Category" ]
• j:vmAddresses[0] or object_pick(j:vmAddresses[0],'Address', 'Category') <-- returns {"Address": { "string": "addr1" }, "Category": { "string": "order" } }

Which value are you trying to parse? Everything seems working.

Additional answers based on comment:

You can use object_construct to build the JSON after reading the values with the vmAddresses[0].Address.string notation:

with json_data as (
select parse_json( '{ "Vendor": {"string": "ABC" }, "vmAddresses": [ { "Address": { "string": "addr1" }, "Category": { "string": "order" } } ] }' ) j)
select OBJECT_CONSTRUCT( 'Address', j:vmAddresses[0].Address.string, 'Category', j:vmAddresses[0].Category.string )
from json_data;