Parsing Nested array within a nested array

Help Procedural SQL
1

I have the below Avro. which has a nested array(attachment) and also a nested array within a nested array( charactersticsRelationship inside characterstic).

{
“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 below code to parse through attachment and insert the data in a table

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 parse charactersticsRelationship inside characterstic and insert data into their respective table. Can anyone please help me with is?

2

This should get you into the direction your needing. Due to everything your looking at is an [] you will find every [] location your needing and move down to the next level and next.

This will get you to the name level your looking for. Will still have to extract name items like Id, href that your looking to extract. Not only way to create this but should be easy to fallow the pattern this way.

SET @myJSON ='{...}';

WITH `event` AS (
	SELECT f.table_col F
	FROM TABLE(JSON_TO_ARRAY(@myJSON::`fields`)) f
	WHERE f.table_col::$`name` = 'event'
)
, communicationMessage AS (
	SELECT f.table_col F
	FROM `event`
	CROSS JOIN TABLE(JSON_TO_ARRAY(`event`.F::`type`::`fields`)) f
	WHERE f.table_col::$`name` = 'communicationMessage'
)
, attachment AS (
	SELECT f.table_col F
	FROM communicationMessage c
	CROSS JOIN TABLE(JSON_TO_ARRAY(c.F::`type`::`fields`)) f
	WHERE f.table_col::$`name` = 'attachment'
)
SELECT
	f.table_col
FROM attachment a
CROSS JOIN TABLE(JSON_TO_ARRAY(a.F::`type`::items::`fields`)) f;

Output will get you to this level:
{“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”]}

3

Hi @markw. I was successful in extracting values associated with attachment. I want to parse charactersticsRelationship inside characteristic and insert data into their respective table.
I am able to fetch values of Characterstics and when i try to parse charactersticsRelationship , extract the vales associated with it and insert it into the table it either gives me null or no output.

4

Best way is to take it one level at a time and inspect the JSON you have to work with at every level.

WITH `event` AS (
	SELECT f.table_col F
	FROM TABLE(JSON_TO_ARRAY(@myJSON::`fields`)) f
	WHERE f.table_col::$`name` = 'event'
)
, communicationMessage AS (
	SELECT f.table_col F
	FROM `event`
	CROSS JOIN TABLE(JSON_TO_ARRAY(`event`.F::`type`::`fields`)) f
	WHERE f.table_col::$`name` = 'communicationMessage'
)
, characteristic AS (
	SELECT f.table_col F
	FROM communicationMessage c
	CROSS JOIN TABLE(JSON_TO_ARRAY(c.F::`type`::`fields`)) f
	WHERE f.table_col::$`name` = 'characteristic'
)
, characteristicRelationship AS (
	SELECT f.table_col F
	FROM characteristic c
	CROSS JOIN TABLE(JSON_TO_ARRAY(c.F::`type`::items::`fields`)) f
	WHERE f.table_col::$`name` = 'characteristicRelationship'
)
SELECT
	f.table_col
FROM characteristicRelationship a
CROSS JOIN TABLE(JSON_TO_ARRAY(a.F::`type`::items::`fields`)) f;

Output will get you to this level:
{“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”]}

or field level

...
SELECT
	 f.table_col::`default` 'default'
	,f.table_col::$`name` 'name'
    ,f.table_col::`type` 'type'
FROM characteristicRelationship a
CROSS JOIN TABLE(JSON_TO_ARRAY(a.F::`type`::items::`fields`)) f;
5

Thanks for you suggestion. I was able to resolve this issue

6

"INSERT INTO characteristicRelationship(char_relation_id,relationshipType,baseType,schemaLocation,characterstic_type)

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;"

7

Glad you were able to get it working.

After I loaded the supplied example JSON into a table named q field named msg. I am not able to get output from the supplied example in SingleStore 8.0.20.

I’m not aware of a way to reference the value in a field in this case “name” (not identifying the field source) and also skipping over “fields” [].

What version of SingleStore are you using?

8

I’m using the version 8.1.14