Hello,
I'm trying to create an API profile where I insert 1 row for each available object in the JSON file. I have a table in SNOWFLAKE of type variant that will store full object.
This is the structure of my JSON file:
[
{
"ID": 147852,
"Date": "2024-03-25",
"tags": [
{
"attributeName": "TCT",
"value": "Mon Mar 25 11:44:55 EDT 2024"
},
{
"attributeName": "FHF",
"value": 5455525.0
}
],
"currentPosition": 13654.0
},
{
"ID": 698452,
"Date": "2024-03-05",
"tags": [
{
"attributeName": "TCT",
"value": "Mon Mar 25 11:44:55 EDT 2024"
},
{
"attributeName": "FHF",
"value": 5455525.0
}
],
"currentPosition": 4000000.0
},
{
"ID": 548561,
"Date": "2024-04-15",
"tags": [
{
"attributeName": "FHF",
"value": 651656561.0
},
{
"attributeName": "TCT",
"value": "Thu Apr 25 04:29:41 EDT 2024"
}
]
}
]
The requirement is to store in the snowflake variant column in 3 rows.
First row would be :
{
"ID": 147852,
"Date": "2024-03-25",
"tags": [
{
"attributeName": "TCT",
"value": "Mon Mar 25 11:44:55 EDT 2024"
},
{
"attributeName": "FHF",
"value": 5455525.0
}
],
"currentPosition": 13654.0
}
In the API query profile I created 1 attribute like below:
<attr name="JSON" xs:type="Variant" readonly="false" other:xPath="JSON" other:valueFormat="aggregate"/>
The API works and insert 3 rows but all values are null.
I'm not sure what I am doing wrong. Please help.
Thank you in advance.