How can I return an ARRAY/JSON structure in a column in postgresql?

Question

Hello I have a set of tables as follows to explain my issue with a minimal example (I want to know how can I retrieve that structure, not change the database structure):

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

I wan to get this structure as follows: For a given 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",
                }
            ]
        }
    ]
}

So I started with something like this

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'

Now I'm not able to know how can I return that array inside a column, or create a JSON with the whole response. I have been already some hours trying to use some JSON PATH functions I have been suggested in some questions but I am not able to make them work.

Could someone give me a hint using this simple example?

Akhilesh Mishra · Accepted Answer · 2020-11-10 19:09:54Z

2

Your output structure is not a standard JSON format. It should be : instead of = between key and value. Considering you want standard JSON output, try below mentioned query:

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'

DEMO

Above query will return a row with JSON value for each fruit if you will not use where clause.

If you literally want the output as you have mentioned in your question then replace row_to_json(d2) with replace(row_to_json(d2)::text,':', ' = ') in above query.

edited Nov 10, 2020 at 19:09

answered Nov 10, 2020 at 18:00

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

nck Over a year ago

Thanks that resolved the question, but now I was trying to do the same in my project, and I need to get deeper in the structure, for example having a table to be called from fruit_varieties having there a colors table with names and linking it with a color_id, being possilble to have more than one color. I have tried extending this query but i get an error aggregate function calls cannot be nested

Akhilesh Mishra Over a year ago

Just add example in your question. I will update the answer accordingly. from your error it seems your trying to call the json_agg in side it.

nck Over a year ago

I extended the question puting another table which goes nested from the other nested array. I think that example illustrates my issue

Akhilesh Mishra Over a year ago

@nck updated the answer as per you new requirement. Please note you need to write the sub query for each nested array

nck Over a year ago

@ Akhilesh Mishra Thanks a lot it works, I'D had never though about that solution

Collectives™ on Stack Overflow

How can I return an ARRAY/JSON structure in a column in postgresql?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related