Блог

Здравствуйте, у меня есть набор таблиц следующим образом, чтобы объяснить мою проблему на минимальном примере (я хочу знать, как я могу получить эту структуру, а не изменять структуру базы данных):

 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 функции, которые мне предлагали в некоторых вопросах, но я не могу заставить их работать.

Может кто-нибудь дать мне подсказку, используя этот простой пример?

Ваша структура вывода не является стандартным форматом 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,':', ' = ') в приведенном выше запросе.