CODEWITHSUNDEEP
sqlarraysjsonpostgresqlsubquery
nck
nck

Reputation: 2221

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

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?

Upvotes: 0

Views: 91

Answers (1)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6130

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.

Upvotes: 2

Related Questions