Parsing Nested array within a nested array in MemSQL

Question

I am trying to extract data from Avro schema and insert the data to db in MemSQL. I have the below Avro Schema

{
      "doc": "Sample schema to help you get started.",
      "fields": [
        {
          "name": "event",
          "type": {
            "fields": [
              {
                "name": "communicationMessage",
                "type": {
                  "fields": [
                    {
                      "name": "characteristic",
                      "type": {
                        "items": {
                          "fields": [
                            {
                              "default": null,
                              "name": "id",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "default": null,
                              "name": "name",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "default": null,
                              "name": "value",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "default": null,
                              "name": "valueType",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "name": "characteristicRelationship",
                              "type": {
                                "items": {
                                  "fields": [
                                    {
                                      "default": null,
                                      "name": "id",
                                      "type": [
                                        "null",
                                        "string"
                                      ]
                                    },
                                    {
                                      "default": null,
                                      "name": "relationshipType",
                                      "type": [
                                        "null",
                                        "string"
                                      ]
                                    },
                                    {
                                      "default": null,
                                      "name": "baseType",
                                      "type": [
                                        "null",
                                        "string"
                                      ]
                                    },
                                    {
                                      "default": null,
                                      "name": "schemaLocation",
                                      "type": [
                                        "null",
                                        "string"
                                      ]
                                    },
                                    {
                                      "default": null,
                                      "name": "type",
                                      "type": [
                                        "null",
                                        "string"
                                      ]
                                    }
                                  ],
                                  "name": "characteristicRelationship",
                                  "type": "record"
                                },
                                "type": "array"
                              }
                            },
                            {
                              "default": null,
                              "name": "baseType",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "default": null,
                              "name": "schemaLocation",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "default": null,
                              "name": "type",
                              "type": [
                                "null",
                                "string"
                              ]
                            }
                          ],
                          "name": "characteristic",
                          "type": "record"
                        },
                        "type": "array"
                      }
                    },
                    {
                      "name": "attachment",
                      "type": {
                        "items": {
                          "fields": [
                            {
                              "default": null,
                              "name": "id",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "default": null,
                              "name": "href",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "default": null,
                              "name": "attachmentType",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "default": null,
                              "name": "content",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "default": null,
                              "name": "description",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "default": null,
                              "name": "mimeType",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "default": null,
                              "name": "url",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "default": null,
                              "name": "name",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "name": "size",
                              "type": {
                                "fields": [
                                  {
                                    "default": null,
                                    "name": "amount",
                                    "type": [
                                      "null",
                                      "int"
                                    ]
                                  },
                                  {
                                    "default": null,
                                    "name": "units",
                                    "type": [
                                      "null",
                                      "string"
                                    ]
                                  }
                                ],
                                "name": "size",
                                "type": "record"
                              }
                            },
                            {
                              "name": "validFor",
                              "type": {
                                "fields": [
                                  {
                                    "default": null,
                                    "name": "endDateTime",
                                    "type": [
                                      "null",
                                      "string"
                                    ]
                                  },
                                  {
                                    "default": null,
                                    "name": "startDateTime",
                                    "type": [
                                      "null",
                                      "string"
                                    ]
                                  }
                                ],
                                "name": "validFor",
                                "type": "record"
                              }
                            },
                            {
                              "default": null,
                              "name": "baseType",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "default": null,
                              "name": "schemaLocation",
                              "type": [
                                "null",
                                "string"
                              ]
                            },
                            {
                              "default": null,
                              "name": "type",
                              "type": [
                                "null",
                                "string"
                              ]
                            }
                          ],
                          "name": "attachment",
                          "type": "record"
                        },
                        "type": "array"
                      }
                    }
                    ],
                  "name": "communicationMessage",
                  "type": "record"
                }
              }
            ],
            "name": "event",
            "type": "record"
          }
        }
      ],
      "name": "example",
      "namespace": "com.example",
      "type": "record"
    }

I have written the below code to parse the nested arryay [Attachment] and insert into a table -Attachment

insert into attachment
select  ad::id::$string ,ad::href::$string , ad::attachmentType::$string , ad::content::$string ,ad::description::$string , ad::mimeType::$string ,ad::url::$string ,ad::name::$string ,ad::size::amount,
ad::size::units::$string ,ad::validFor::endDateTime::$string , ad::validFor::startDateTime::$string ,ad::baseType::$string , ad::schemaLocation::$string , ad::type::$string
from(select table_col as ad  from q join table (JSON_TO_ARRAY(msg::event::communicationMessage::attachment)));

I want to insert data to characterstics table by parsing characterstics and charactersticsRelationship [ nested array within a nested array]. IS there a way i can do this in MemSQL?

Answer 1

I was able to resolve this and parse through a nested array within a nested array.

"INSERT INTO characteristicRelationship
SELECT inner_table.table_col::id::$string,inner_table.table_col::relationshipType::$string,
inner_table.table_col::baseType::$string,inner_table.table_col::schemaLocation::$string,
inner_table.table_col::type::$string
FROM q JOIN
TABLE(JSON_TO_ARRAY(msg::event::communicationMessage::characteristic)) AS outer_table JOIN
TABLE(JSON_TO_ARRAY(outer_table.table_col::`characteristicRelationship`)) AS inner_table;"