#sql #arrays #json #postgresql #subquery
#sql #массивы #json #postgresql #подзапрос
Вопрос:
Здравствуйте, у меня есть набор таблиц следующим образом, чтобы объяснить мою проблему на минимальном примере (я хочу знать, как я могу получить эту структуру, а не изменять структуру базы данных):
fruit:
id | name | form| texture_id
-------------------------------
1 | Apple | round | 1
2 | Banana | long | 1
fruit_varieties:
id | name | fruit_id | color
-------------------------------
1 | golden | 1| green
2 | fuji | 1| red
3 | canarias | 2| yellow
fruit_texture
id | name
-------------------------------
1 | soft
2 | hard
variety_countries
id | name | fruit_variety_id | average_temperature
-------------------------------
1 | france | 1 | 21
2 | spain | 1 | 24
3 | italy | 2 | 23
Я хочу получить эту структуру следующим образом:
Для данного fruit.name=Apple
:
{
"fruit_name" = "Apple",
"form" = "round",
"texture" = "soft",
"fruit_properties" = [{
"variety_name" = "Golden",
"color" = "green",
"countries" = [{
"country" = "france",
"avg_temperature" = "21",
}, {
"country" = "spain",
"avg_temperature" = "24",
}
]
}, {
"variety_name" = "fuji",
"color" = "red",
"countries" = [{
"country" = "italy",
"avg_temperature" = "23",
}
]
}
]
}
Итак, я начал с чего-то вроде этого
SELECT
fruit.name AS fruit_name,
fruit.form AS form,
fruit_texture.name AS texture,
(
# I don't know how to handle this
) AS fruit_properties
FROM fruit
JOIN fruit_varieties
ON fruit.id = fruit_varieties.fruit_id
WHERE fruit.name = 'Apple'
Теперь я не могу знать, как я могу вернуть этот массив внутри столбца или создать JSON со всем ответом. Я уже несколько часов пытаюсь использовать некоторые JSON PATH
функции, которые мне предлагали в некоторых вопросах, но я не могу заставить их работать.
Может кто-нибудь дать мне подсказку, используя этот простой пример?
Ответ №1:
Ваша структура вывода не является стандартным форматом JSON. Это должно быть :
вместо =
между ключом и значением. Учитывая, что вам нужен стандартный вывод JSON, попробуйте выполнить приведенный ниже запрос:
select row_to_json(d2) from (
select
name,
form,
texture,
json_agg(json_build_object('variety_name',variety_name,'color',color,'countries',countries)) "fruit_properties"
from
(
select
t1.name "name",
t1.form "form",
t3.name "texture",
t2.name "variety_name",
t2.color "color",
json_agg(json_build_object( 'country',t4.name,'temp',t4.average_temperature)) "countries"
from
fruit t1 inner join fruit_varieties t2 on t1.id=t2.fruit_id
inner join fruit_texture t3 on t1.texture_id=t3.id
inner join variety_countries t4 on t4.fruit_variety_id=t2.id
group by 1,2,3,4,5
) d1
group by 1,2,3
) d2
where d2.name='Apple'
Приведенный выше запрос вернет строку со JSON
значением для каждого фрукта, если вы не будете использовать предложение where.
Если вы буквально хотите получить результат, как вы упомянули в своем вопросе, тогда замените row_to_json(d2)
на replace(row_to_json(d2)::text,':', ' = ')
в приведенном выше запросе.
Комментарии:
1. Спасибо, что решил вопрос, но теперь я пытался сделать то же самое в своем проекте, и мне нужно глубже разобраться в структуре, например, вызвать таблицу, из
fruit_varieties
которой будет вызываться таблица цветовnames
, и связать ее с acolor_id
, возможно, иметь более одного цвета. Я попытался расширить этот запрос, но я получаю сообщение об ошибкеaggregate function calls cannot be nested
2. Просто добавьте пример в свой вопрос. Я соответствующим образом обновлю ответ. из вашей ошибки кажется, что вы пытаетесь вызвать json_agg в стороне от него.
3. Я расширил вопрос, добавив другую таблицу, которая является вложенной из другого вложенного массива. Я думаю, что этот пример иллюстрирует мою проблему
4. @nck обновил ответ в соответствии с вашими новыми требованиями. Пожалуйста, обратите внимание, что вам необходимо написать вложенный запрос для каждого вложенного массива
5. @ Akhilesh Mishra Большое спасибо, это работает, я никогда не думал об этом решении