Accessing Items in an Array
Use the dot notation to query nested elements and the array notation for selecting items in an array.
select
nestedObj.a,
nestedArr[0],
nestedObj.secondLevel.x as Second Level Object,
nestedObj.secondLevel.y[1] as Second Level Array,
sent
where nestedArr[0]=150;
Example
Accessing Instrument Readings from a JSON File:
{
"sensorData": {
"temperature": {
"current": 72.5,
"readings": [71.2, 72.5, 73.0]
},
"pressure": {
"current": 1012.3,
"readings": [1010.5, 1011.8, 1012.3]
}
},
"timestamps": ["2024-09-20", "2024-09-21", "2024-09-22"],
"status": true
}
Cloud9QL:
select sensorData.temperature.current,
timestamps[0],
sensorData.pressure.current as CurrentPressure,
sensorData.temperature.readings[1] as PreviousTemperatureReading;
Result:
-
sensorData.temperature.current
= 72.5 (current temperature) -
timestamps[0]
= "2024-09-20" (first timestamp) -
CurrentPressure
= 1012.3 (current pressure) -
PreviousTemperatureReading
= 72.5 (second temperature reading)
Unwind Arrays using Expand
To unwind/expand an array, use the expand syntax.
Example:
select customer, nestedObj.secondLevel.y as Nested;
select expand(Nested);
Note that expand must be specified on its own, without any other elements within the select.
View the Cloud9QL expand documentation here.