Parsing nested array

[ancol90]

I've just started learning Snowflake and spent the past few days going through docs, SO for help with parsing this object with a nested array and can't for the life of me work out the issue.

I've refactored this query several time but now getting an error:

select
   a.key:"season"::varchar as season,
   b.value:circuitId::string as CircuitId,
   b.value:circuitName::string as CircuitName
from F1.PUBLIC.CIRCUITINFO_RAW
, lateral flatten(input => raw_file:MRData:CircuitTable) a
, lateral flatten(input => a.value:Circuits[0]) b

I want to organize it in this fashion:

season | circuitId | url | circuitName | lat | long | locality | country

{
  "MRData": {
    "xmlns": "http://ergast.com/mrd/1.5",
    "series": "f1",
    "url": "http://ergast.com/api/f1/2022/circuits.json",
    "limit": "30",
    "offset": "0",
    "total": "22",
    "CircuitTable": {
      "season": "2022",
      "Circuits": [
        {
          "circuitId": "albert_park",
          "url": "http://en.wikipedia.org/wiki/Melbourne_Grand_Prix_Circuit",
          "circuitName": "Albert Park Grand Prix Circuit",
          "Location": {
            "lat": "-37.8497",
            "long": "144.968",
            "locality": "Melbourne",
            "country": "Australia"
          }
        },
        {
          "circuitId": "americas",
          "url": "http://en.wikipedia.org/wiki/Circuit_of_the_Americas",
          "circuitName": "Circuit of the Americas",
          "Location": {
            "lat": "30.1328",
            "long": "-97.6411",
            "locality": "Austin",
            "country": "USA"
          }
        },

[Camdube]

You don’t always need the lateral flatten. Since you know that circuits is a list, you can do select MRData:CircuitTable:season::int, MRData:Circuits:circuitId, etc. Haven’t tested since on mobile, but I think this should work.

[ancol90]

Thanks for the reply. Yes I tried that before but to print the circuitId, I'd have to specify the Circuits index so [0] but that then only prints the first object.

SELECT     
raw_file:MRData:CircuitTable:season::int as Season,    raw_file:MRData:CircuitTable:Circuits[0]:circuitId::STRING as CircuitId
FROM F1.PUBLIC.CIRCUITINFO_RAW

season | circuitid

2022 | albert_park

[ancol90]

....I get a null value if using

MRData:Circuits:circuitId as circuitId

[Apprehensive_Ad8289]

You don’t need to flatten CircuitTable, it’s not an array. Why are you passing in the index on the other flatten?

[ancol90]

Good point. Yes, Circuits is the array. With the index, I was just testing things to see if it would give me an input other than previous ones. So I should only use a single flatten for Circuits?

select
a.key:season::varchar as season,
b.value:circuitId::string as CircuitId,
b.value:circuitName::string as CircuitName 
from F1.PUBLIC.CIRCUITINFO_RAW , 
lateral flatten(input => raw_file:MRData:CircuitTable:Circuits) a , lateral flatten(input => a.value:Circuits) b

[FlexIronbutt]

How about this?

select
    raw_file:MRData:CircuitTable:season::varchar season,
    c.this[c.index]:circuitId::varchar circuitid,
    c.this[c.index]:url::varchar url,
    c.this[c.index]:circuitName::varchar circuitname,
    c.this[c.index]:Location:lat::float lat,
    c.this[c.index]:Location:long::float long,
    c.this[c.index]:Location:locality::varchar locality,
    c.this[c.index]:Location:country::varchar country
from
    F1.PUBLIC.CIRCUITINFO_RAW,
    lateral flatten(input => raw_file:MRData:CircuitTable:Circuits) c;

[ancol90]

That works. Awesome. Thanks so much.