добрый день.
подскажите в парсинге. есть массив в таблице, выглядит вот так:
{
"city": [
{
"id": "1",
"name": "Санкт-Петербург",
"code": "7800000000000000000000000",
"cityID": 200601,
"latitude": "59.936600",
"longitude": "30.412800",
"url": "spb.dellin.ru",
"timeshift": "0",
"requestEndTime": "23:59",
"sfrequestEndTime": "18:00",
"day2dayRequest": "1",
"day2daySFRequest": "0",
"preorderRequest": "1",
"freeStorageDays": "2",
"terminals": {
"terminal": [
{
"id": "1",
"name": "Санкт-Петербург Парнас",
"address": "1-й Верхний пер., 12б",
"fullAddress": "194292, Санкт-Петербург г, 1-й Верхний пер, дом № 12, Литера Б",
"latitude": "60.063000",
"longitude": "30.381800",
"phones": [
{
"number": "7 (812) 448-88-88",
"type": "городской",
"comment": "",
"primary": false
}
],
"storage": true,
"mail": "pismo@dellin.ru",
"mainPhone": "7 (812) 448-88-88",
"isPVZ": false,
"isOffice": false,
"receiveCargo": true,
"giveoutCargo": true,
"maps": {
"width": {
"640": {
"height": {
"640": {
"url": " http://assets.dellin.ru/assets/yamaps_public/2015/7/22/843804/original/1.png#40719c3f37a2a38da55e5e5528c1dc84a42846a29bec7693992f2a3ee58f8c86"
}
}
},
"944": {
"height": {
"352": {
"url": " http://assets.dellin.ru/assets/yamaps_public/2015/7/22/843805/original/1.png#5e188788dab8b97000dac9cdb11a704539ba28efac1a3d490bbc556754e9cdd2"
}
}
},
"960": {
"height": {
"960": {
"url": " http://assets.dellin.ru/assets/yamaps_public/2015/7/22/843806/original/1.png#951694015fc369ec4468258ed1b232aa70b823463c8a8a686fc5ac86f9f0568f"
}
}
}
}
},
"addressCode": {
"street_code": "7800000000015870000000000"
},
"calcSchedule": {
"derival": "пн-пт: 08:00-20:00; сб, вс: 10:00-16:00",
"arrival": "пн-пт: 08:00-20:00; сб, вс: 10:00-16:00"
},
"default": true,
"maxWeight": 3000.0,
"maxLength": 12.0,
"maxWidth": 2.4,
"maxHeight": 2.3,
"maxVolume": 0.0,
"maxShippingWeight": 0.0,
"maxShippingVolume": 0.0,
"worktables": {
"worktable": [
{
"department": "Приём и выдача груза",
"monday": "08:00-20:00",
"tuesday": "08:00-20:00",
"wednesday": "08:00-20:00",
"thursday": "08:00-20:00",
"friday": "08:00-20:00",
"saturday": "10:00-16:00",
"sunday": "10:00-16:00",
"timetable": "пн-пт 08:00-20:00; сб,вс 10:00-16:00"
},
{
"department": "Доставка груза",
"monday": "24 ч",
"tuesday": "24 ч",
"wednesday": "24 ч",
"thursday": "24 ч",
"friday": "24 ч",
"saturday": "24 ч",
"sunday": "24 ч",
"timetable": "пн-вс круглосуточно"
},
{
"department": "Ответственное хранение: приём и выдача груза",
"monday": "09:00-18:00",
"tuesday": "09:00-18:00",
"wednesday": "09:00-18:00",
"thursday": "09:00-18:00",
"friday": "09:00-18:00",
"saturday": "-",
"sunday": "-",
"timetable": "пн-пт 09:00-18:00; сб-вс выходной"
}
]
}
},
{
"id": "39",
"name": "Санкт-Петербург офис",
"address": "ул. Стартовая, 8А",
"fullAddress": "196210, Санкт-Петербург г, Стартовая ул, дом № 8, Литера А, офис 132",
"latitude": "59.806300",
"longitude": "30.310900",
"phones": [
{
"number": "7 (812) 448-88-88",
"type": "городской",
"comment": "",
"primary": false
}
],
"storage": false,
"mail": "pismo@dellin.ru",
"mainPhone": "7 (812) 448-88-88",
"isPVZ": false,
"isOffice": true,
"receiveCargo": false,
"giveoutCargo": false,
"maps": {
"width": {
"640": {
"height": {
"640": {
"url": " http://assets.dellin.ru/assets/yamaps_public/2015/7/22/843814/original/39.png#40719c3f37a2a38da55e5e5528c1dc84a42846a29bec7693992f2a3ee58f8c86"
}
}
},
"944": {
"height": {
"352": {
"url": " http://assets.dellin.ru/assets/yamaps_public/2015/7/22/843815/original/39.png#5e188788dab8b97000dac9cdb11a704539ba28efac1a3d490bbc556754e9cdd2"
}
}
},
"960": {
"height": {
"960": {
"url": " http://assets.dellin.ru/assets/yamaps_public/2015/7/22/843816/original/39.png#951694015fc369ec4468258ed1b232aa70b823463c8a8a686fc5ac86f9f0568f"
}
}
}
}
},
"addressCode": {
"street_code": "7800000000018790000000000"
},
"calcSchedule": {
"derival": "",
"arrival": ""
},
"default": false,
"maxWeight": 3000.0,
"maxLength": 13.0,
"maxWidth": 2.4,
"maxHeight": 2.4,
"maxVolume": 0.0,
"maxShippingWeight": 0.0,
"maxShippingVolume": 0.0,
"worktables": {
"worktable": [
{
"department": "Офис",
"monday": "09:00-19:00",
"tuesday": "09:00-19:00",
"wednesday": "09:00-19:00",
"thursday": "09:00-19:00",
"friday": "09:00-19:00",
"saturday": "-",
"sunday": "-",
"timetable": "Прием/выдача груза/оплата перевозок не осуществляются "
},
{
"department": "Доставка груза",
"monday": "24 ч",
"tuesday": "24 ч",
"wednesday": "24 ч",
"thursday": "24 ч",
"friday": "24 ч",
"saturday": "24 ч",
"sunday": "24 ч",
"timetable": "пн-вс круглосуточно"
}
]
}
},
нужно запросом вытянуть целый массив данных, на данный момент получается только построчно
запрос выглядит так:


select json_value(jto.json_return, '$.city[1].name') as name1
/* json_value(json_return, '$.city[*].code') as code,
json_value(json_return, '$.city[*].cityID') as cityid,
json_value(json_return, '$.city[*].terminals.terminal[*].id') as ad_id,
json_value(json_return, '$.city[*].terminals.terminal[*].fullAddress') as fullAddress,
json_value(json_return, '$.city[*].terminals.terminal[*].addressCode') as addressCode,
json_value(json_return, '$.city[*].terminals.terminal[*].maxVolume') as maxVolume,
json_value(json_return, '$.city[*].terminals.terminal[*].maxWeight') as maxWeight,
json_value(json_return, '$.city[*].terminals.terminal[*].maxWidth') as maxWidth,
json_value(json_return, '$.city[*].terminals.terminal[*].maxLength') as maxLength*/
from json_test_otm jto
where jto.id = 1