Fetching element from multiple nested array

SQL++
1

Hi All,
I need to create query to fetch below fields out of nested json. whats the best way to achieve the same. we are able querying json at depth of level using unnest function.We have to query 2-3 level of json.

Below are fields to be fetched in query
select itnryBook.hotelReservations.hotelReservation.hotelSegment.checkInDate.localTime,
paymentSummary.payment.cardPayment.billingAddress from “bucket” where conditon=?

{
"MTR": {
"itnryBook": {

"hotelReservations": {
"hotelReservation": [
{
"hotelSegment": [
{
"checkInDate": {
"localTime": "2018-11-07"
},
"checkOutDate": {
"localTime": "2018-11-08"
},
"confirmationNumber": "PSZBA2",
"hotel": {
"brandCd": "Extended Stay America",
"chainCd": "840",
"contactInfo": {
"callInInfo": {},
"mailingInfo": {
"address": [
{
"addressLine1": "3421 East Elwood St",
"addressLine2": "3421 East Elwood St",
"cityName": "Phoenix",
"coordinates": {
"latitude": "0.0",
"longitude": "0.0"
},
"countryCd": "US",
"stateProvinceCd": "AZ",
"zipCd": "85040"
}
]
}
},
"name": "Extended Stay America - Phoenix - Airport",
"policies": {
"cancellationPolicy": [
"This reservation is non-refundable. Cancellations or changes made at any time are subject to a 100% charge."
]
},
"propertyNumber": 9716,
"starRating": "3"
},
"pricing": {
"payment": {
"payment": [
{
"paymentID": [
"01"
]
}
]
},
"roomPrice": {
"dailyRateBreakdown": [
{
"baseFare": {
"supplierFare": {
"amount": 71.99,
"currency": "USD"
},
"systemFare": {
"amount": 71.99,
"currency": "USD"
}
},
"noofNights": 1,
"totalFare": {
"supplierFare": {
"amount": 71.99,
"currency": "USD"
},
"systemFare": {
"amount": 71.99,
"currency": "USD"
}
}
}
]
},
"tax": {
"supplierFare": {
"amount": 9.06,
"currency": "USD"
},
"systemFare": {
"amount": 9.06,
"currency": "USD"
}
},
"totalPrice": {
"supplierFare": {
"amount": 81.05,
"currency": "USD"
},
"systemFare": {
"amount": 81.05,
"currency": "USD"
}
}
},
"room": {
"guest": {
"paxRPH": [
{
"value": "00.01"
}
]
},
"guestCount": "0",
"maxPersons": "0",
"primaryPaxRPH": "00.01",
"roomType": {
"code": "206288",
"description": "Studio, 1 Queen Bed, Non Smoking - 1 queen bed"
}
},
"salesModel": "Pay Now",
"status": "Success"
}
],
"identifier": {
"inventorySystem": "Orbitz",
"inventorySystemType": "OTA",
"recordLocator": "PBAXP6211403644"
},
"programs": {
"program": [
""
]
},
"reservationRPH": "${trip1}-${trip2}-01",
"total": {
"systemFare": {
"amount": 81.05,
"currency": "USD"
}
},
"transactionTime": {
"createTime": {
"systemTime": "2018-09-11"
},
"lastUpdatedTime": {
"systemTime": "2018-09-11 13:43:05.0"
}
}
}
]
},
"paymentSummary": {
"payment": [
{
"cardPayment": {
"card": {
"billingAddress": {
"addressLine1": "2313 EAST DRYWOOD ROAD",
"cityName": "PHOENIX",
"country": "US",
"countryCd": "US",
"stateProvince": "AZ",
"stateProvinceCd": "AZ",
"zipCd": "850248688"
},
},
"creditAmount": {
"systemFare": {
"amount": 81.05,
"currency": "USD"
}
}
},
"paymentAmount": {
"systemFare": {
"amount": 81.05,
"currency": "USD"
}
},
"paymentID": "01",
"paymentType": "CREDIT_CARD"
}
]
}

}
}
"type": "test"
}
2 
SELECT
    ARRAY_FLATTEN(ARRAY (ARRAY hs.checkInDate.localTime
                         FOR hs IN  hr.hotelSegment
                         END)
                  FOR hr IN b.itnryBook.hotelReservations.hotelReservation
                  END,
                1) localTimes,
   ARRAY payment.cardPayment.card.billingAddress
   FOR payment IN b.itnryBook.paymentSummary.payment
   END billingAddresses
FROM bucket AS b
WHERE b.type = "test" AND ...;

If you still need help please post the exact WHERE clause and what is exact output with multiple items in ARRAYS.

3

@vsr1
thanks for help but its getting below error
[
{
“code”: 3000,
“msg”: “syntax error - at FOR”,
“query_from_user”: “SELECT\r\nARRAY AARRAY_FLATTEN((ARRAY hs.checkInDate.localTime FOR hs IN hr.checkInDate.localTime END) FOR hr IN b.itnryBook.hotelReservations END,2) localTimes\r\n,ARRAY payment.cardPayment.card.billingAddress FOR payment IN b.itnryBook.paymentSummary.payment END billingAddresses\r\nFROM MTR AS b\r\nWHERE where b.prtrBookReferId=”{__Random(1000,9999,trip1}-{__Random(0001,9999,trip2)}" and \r\nb.type='travel_itinerary"
}
]

query fired

SELECT
ARRAY AARRAY_FLATTEN((ARRAY hs.checkInDate.localTime FOR hs IN hr.checkInDate.localTime END) FOR hr IN b.itnryBook.hotelReservations END,2) localTimes
,ARRAY payment.cardPayment.card.billingAddress FOR payment IN b.itnryBook.paymentSummary.payment END billingAddresses
FROM TEST AS b
WHERE b.prtrBookReferId="?" and
b.type=’?’;

4

Fixed and try the query again

5

Thanks again,
still we are getting result as null . I am posting query executed and nested loop.
SELECT
ARRAY_FLATTEN(ARRAY (ARRAY hs.checkInDate.localTime FOR hs IN hr.checkInDate.localTime END) FOR hr IN
b.itnryBook.hotelReservations END,2) localTimes,
ARRAY payment.cardPayment.card.billingAddress FOR payment IN b.itnryBook.paymentSummary.payment END billingAddresses
FROM MTR AS b
WHERE b.prtrBookReferId="${__Random(1000,9999,trip1}-${__Random(0001,9999,trip2)}" and
b.type=’?’;

[
{
“billingAddresses”: [
{
“addressLine1”: “2313 EAST DRYWOOD ROAD”,
“cityName”: “PHOENIX”,
“country”: “US”,
“countryCd”: “US”,
“stateProvince”: “AZ”,
“stateProvinceCd”: “AZ”,
“zipCd”: “850248688”
}
],
“localTimes”: null
}
]

6 
SELECT
    ARRAY_FLATTEN(ARRAY (ARRAY hs.checkInDate.localTime
                         FOR hs IN  hr.hotelSegment
                         END)
                  FOR hr IN b.itnryBook.hotelReservations.hotelReservation
                  END,
                1) localTimes,
   ARRAY payment.cardPayment.card.billingAddress
   FOR payment IN b.itnryBook.paymentSummary.payment
   END billingAddresses
FROM default AS b
WHERE b.type = "test";
1 Like
7

@vsr1 thanks a lot for help. I am trying to create query to (fetch some report) extract element on 4 nested level based on your help will keep you posted on same. thanks a alot.