我有这个疑问，我不知道什么是错的？
我想从中提取Name信息，其中包含产品的名称以及每个产品带来的数量。如何修改查询以使其工作？
SELECT o.id,
d->>'name' AS "Unidades"
FROM rtdm_order o
CROSS JOIN LATERAL (
SELECT value->>'name'
FROM jsonb::->> o.integration_raw_data->>'details' AS value
) AS d
WHERE o.restaurant_id = '1'
AND created_timestamp BETWEEN '2023-09-11' AND '2023-09-23'
AND d->>'name' IS NOT NULL;误差
ERROR: syntax error at or near "::"
LINE 6: FROM jsonb::->> o.integration_raw_data->>'details' AS va...
^
SQL state: 42601
Character: 125JSON
{
"origin": "WEB",
"restaurantId": 37,
"notes": "",
"restaurantName": "xxxxx",
"utm": {
"utm_campaign": null,
"utm_content": null,
"utm_medium": "xxxx",
"utm_source": "xxxxl",
"utm_term": null
},
"registeredDate": "2023-09-13 19:08:47.754",
"id": 5226408,
"user": {
"lastName": "xxxxxe",
"isNew": false,
"id": 180051,
"firstName": "xxxxa ",
"email": "xxxxxxxx"
},
"address": {
"area": "",
"tel": "5491123573490",
"extra": "",
"crossStreets": "",
"notes": "",
"cel": "91123573490",
"manual": false,
"zipCode": "",
"phone": "xxxxx / xxxxx",
"street": "",
"location": null,
"streetNumber": "",
"city": null,
"streetName": ""
},
"deliveryDate": "2023-09-13 20:45:00.000",
"payment": {
"paymentAmount": 18000,
"currencyCode": "ARS",
"currencySymbol": "$",
"shipping": 0,
"notes": "",
"online": false,
"total": 17810,
"subTotal": 17810,
"method": "Efectivo"
},
"pickUp": true,
"details": [
{
"categoryName": "Rolls",
"optionsGroups": [
{
"name": "Tama\u00f1o",
"modifiesBasePrice": true,
"options": [
{
"name": "5 unidades",
"price": 0,
"externalName": "",
"externalId": "2",
"id": 395660,
"quantity": 1
}
],
"externalId": "",
"sortOrder": 1,
"basePrice": 0,
"id": 108252
}
],
"ingredients": [],
"price": 2250,
"externalName": "New York",
"externalId": "2",
"notes": "",
"quantity": 1,
"subtotal": 2250,
"id": 328077,
"name": "New York"
},
{
"categoryName": "Rolls",
"optionsGroups": [
{
"name": "Tama\u00f1o",
"modifiesBasePrice": true,
"options": [
{
"name": "5 unidades",
"price": 0,
"externalName": "",
"externalId": "11",
"id": 395682,
"quantity": 1
}
],
"externalId": "",
"sortOrder": 1,
"basePrice": 0,
"id": 108263
}
],
"ingredients": [],
"price": 2250,
"externalName": "Feel",
"externalId": "6",
"notes": "",
"quantity": 1,
"subtotal": 2250,
"id": 328182,
"name": "Feel"
},
{
"categoryName": "Rolls especiales",
"optionsGroups": [
{
"name": "Tama\u00f1o",
"modifiesBasePrice": true,
"options": [
{
"name": "5 unidades",
"price": 0,
"externalName": "",
"externalId": "19",
"id": 395692,
"quantity": 1
}
],
"externalId": "",
"sortOrder": 1,
"basePrice": 0,
"id": 108268
}
],
"ingredients": [],
"price": 2450,
"externalName": "Ibiza",
"externalId": "19",
"notes": "",
"quantity": 1,
"subtotal": 2450,
"id": 328093,
"name": "Ibiza"
},
{
"categoryName": "Sashimis",
"optionsGroups": [],
"ingredients": [],
"price": 2700,
"externalName": "Sashimi maracuy\u00e1 (5u)",
"externalId": "71",
"notes": "",
"quantity": 1,
"subtotal": 2700,
"id": 328134,
"name": "Sashimi maracuy\u00e1 (5u)"
},
{
"categoryName": "Rolls especiales",
"optionsGroups": [
{
"name": "Tama\u00f1o",
"modifiesBasePrice": true,
"options": [
{
"name": "5 unidades",
"price": 0,
"externalName": "",
"externalId": "8020",
"id": 395788,
"quantity": 1
}
],
"externalId": "",
"sortOrder": 1,
"basePrice": 0,
"id": 108315
}
],
"ingredients": [],
"price": 2450,
"externalName": "",
"externalId": "299",
"notes": "",
"quantity": 1,
"subtotal": 2450,
"id": 328097,
"name": "Tari Tari Roll - NUEVO!"
},
{
"categoryName": "Rolls especiales",
"optionsGroups": [
{
"name": "Tama\u00f1o",
"modifiesBasePrice": true,
"options": [
{
"name": "5 unidades",
"price": 0,
"externalName": "",
"externalId": "23",
"id": 395730,
"quantity": 1
}
],
"externalId": "",
"sortOrder": 1,
"basePrice": 0,
"id": 108287
}
],
"ingredients": [],
"price": 2450,
"externalName": "Bs. As. con Langostino",
"externalId": "23",
"notes": "",
"quantity": 1,
"subtotal": 2450,
"id": 328111,
"name": "Bs. As. con Langostino"
},
{
"categoryName": "Niguiris",
"optionsGroups": [],
"ingredients": [],
"price": 530,
"externalName": "",
"externalId": "50",
"notes": "",
"quantity": 2,
"subtotal": 1060,
"id": 328117,
"name": "Niguiri salm\u00f3n"
},
{
"categoryName": "Niguiris",
"optionsGroups": [],
"ingredients": [],
"price": 2200,
"externalName": "Niguiri Lima (4u)",
"externalId": "135",
"notes": "",
"quantity": 1,
"subtotal": 2200,
"id": 328127,
"name": "Niguiri Lima (4u)"
}
]
}我想从中提取Name信息，其中包含产品的名称以及每个产品带来的数量。如何修改查询以使其工作？
1条答案
按热度按时间enxuqcxy1#
这似乎起作用：
参见：DBFIDDLE
产出：
| ID|名称|数量|
| --|--|--|
| 328077 |“纽约”| 1 |
| 328182 |“感觉”| 1 |
| 328093 |“伊维萨”| 1 |
| 328134 |“生鱼片maracuyá（5 u）”| 1 |
| 328097 |“Tari Tari Roll -努埃沃！“| 1 |
| 328111 |"和兰戈斯蒂诺在一起"| 1 |
| 328117 |“Niguiri salmón”| 2 |
| 328127 |“Niguiri Lima（4u）”| 1 |
此查询遵循逐步过程。
先做
然后执行：
第三步完成了旅程。
(The步骤也存在于DBFIDDLE中）
不幸的是，你没有指定预期的输出，所以我不能检查如果你认为这是正确的。