Create nested json from sql query postgres 9.4
You should build a hierarchical query to get a hierarchical structure as a result.
You want to have many persons in a single json object, so use json_agg()
to gather persons in a json array.
Analogically, a person can have multiple cars and you should place cars belonging to a single person in a json array. The same applies to cars and wheels.
select
json_build_object(
'persons', json_agg(
json_build_object(
'person_name', p.name,
'cars', cars
)
)
) persons
from person p
left join (
select
personid,
json_agg(
json_build_object(
'carid', c.id,
'type', c.type,
'comment', 'nice car', -- this is constant
'wheels', wheels
)
) cars
from
car c
left join (
select
carid,
json_agg(
json_build_object(
'which', w.whichone,
'serial number', w.serialnumber
)
) wheels
from wheel w
group by 1
) w on c.id = w.carid
group by personid
) c on p.id = c.personid;
The (formatted) result:
{
"persons": [
{
"person_name": "Johny",
"cars": [
{
"carid": 1,
"type": "Toyota",
"comment": "nice car",
"wheels": [
{
"which": "front",
"serial number": 11
},
{
"which": "back",
"serial number": 12
}
]
},
{
"carid": 2,
"type": "Fiat",
"comment": "nice car",
"wheels": [
{
"which": "front",
"serial number": 21
},
{
"which": "back",
"serial number": 22
}
]
}
]
},
{
"person_name": "Freddy",
"cars": [
{
"carid": 3,
"type": "Opel",
"comment": "nice car",
"wheels": [
{
"which": "front",
"serial number": 3
}
]
}
]
}
]
}
If you are not familiar with nested derived tables you may use common table expressions.
This variant illustrates that the query should be built starting from the most nested object toward the highest level:
with wheels as (
select
carid,
json_agg(
json_build_object(
'which', w.whichone,
'serial number', w.serialnumber
)
) wheels
from wheel w
group by 1
),
cars as (
select
personid,
json_agg(
json_build_object(
'carid', c.id,
'type', c.type,
'comment', 'nice car', -- this is constant
'wheels', wheels
)
) cars
from car c
left join wheels w on c.id = w.carid
group by c.personid
)
select
json_build_object(
'persons', json_agg(
json_build_object(
'person_name', p.name,
'cars', cars
)
)
) persons
from person p
left join cars c on p.id = c.personid;
How Do I Access Nested JSON in Postgresql 9.4?
To select the meal:
select * from meal where recipe #>> '{meal,0,calories}' = '900';
If you want to find those entries within the array meal
, you have to iterate the array to examine each key. There's no wildcard array index or object name placeholder - you can't write {meal,*,calories}
. Not yet anyway; json functionality continues to improve.
Here's how I'd do it:
select meal.id, recipe_entry
from meal,
lateral jsonb_array_elements(recipe -> 'meal') recipe_entry
where CAST(recipe_entry ->> 'calories' AS integer) = 900;
Some possible future enhancements to json functionality would make this a lot easier. A wildcard-capable path search function that could return a set would be very helpful - probably as enhancements to json_extract_path
. Perhaps in 9.5 if someone proves eager. The other thing that'd really help would be a conversion function or cast for json scalars, so we could write recipe_entry -> 'calories' = to_json(900)
and get Javascript-like equality comparison semantics, rather than relying on the above cast.
Query elements in a nested array of a json object in postgresql 9.4 or 9.5
-- assuming that jsontesting.data contains your JSON
WITH info_data AS (
SELECT enrolment_info->'info' AS info
FROM jsontesting t, json_array_elements(t.data -> 'Data' -> 'enrolmentInfo') AS enrolment_info
)
SELECT info_item->>'courseID',
info_item->>'school',
info_item->>'enrollmentdate',
info_item->>'finshdate',
info_item->>'grade'
FROM info_data idata, json_array_elements(idata.info) AS info_item;
knex postgres join as nested JSON
You can achieve it with one query and join, but it gets overly complicated. You should use an ORM instead.
Objection.js is based on knex and allows you to do this kind of queries in a trivial and performant way (you can choose to use joins or multiple queries, usually multiple queries are more performant).
With Objection.js syntax it would look like this:
const res = await ATableModel.query().where('id', a.id).eager('[b,c]');
Using row_to_json() with nested joins
Update: In PostgreSQL 9.4 this improves a lot with the introduction of to_json
, json_build_object
, json_object
and json_build_array
, though it's verbose due to the need to name all the fields explicitly:
select
json_build_object(
'id', u.id,
'name', u.name,
'email', u.email,
'user_role_id', u.user_role_id,
'user_role', json_build_object(
'id', ur.id,
'name', ur.name,
'description', ur.description,
'duty_id', ur.duty_id,
'duty', json_build_object(
'id', d.id,
'name', d.name
)
)
)
from users u
inner join user_roles ur on ur.id = u.user_role_id
inner join role_duties d on d.id = ur.duty_id;
For older versions, read on.
It isn't limited to a single row, it's just a bit painful. You can't alias composite rowtypes using AS
, so you need to use an aliased subquery expression or CTE to achieve the effect:
select row_to_json(row)
from (
select u.*, urd AS user_role
from users u
inner join (
select ur.*, d
from user_roles ur
inner join role_duties d on d.id = ur.duty_id
) urd(id,name,description,duty_id,duty) on urd.id = u.user_role_id
) row;
produces, via http://jsonprettyprint.com/:
{
"id": 1,
"name": "Dan",
"email": "someemail@gmail.com",
"user_role_id": 1,
"user_role": {
"id": 1,
"name": "admin",
"description": "Administrative duties in the system",
"duty_id": 1,
"duty": {
"id": 1,
"name": "Script Execution"
}
}
}
You will want to use array_to_json(array_agg(...))
when you have a 1:many relationship, btw.
The above query should ideally be able to be written as:
select row_to_json(
ROW(u.*, ROW(ur.*, d AS duty) AS user_role)
)
from users u
inner join user_roles ur on ur.id = u.user_role_id
inner join role_duties d on d.id = ur.duty_id;
... but PostgreSQL's ROW
constructor doesn't accept AS
column aliases. Sadly.
Thankfully, they optimize out the same. Compare the plans:
- The nested subquery version; vs
- The latter nested
ROW
constructor version with the aliases removed so it executes
Because CTEs are optimisation fences, rephrasing the nested subquery version to use chained CTEs (WITH
expressions) may not perform as well, and won't result in the same plan. In this case you're kind of stuck with ugly nested subqueries until we get some improvements to row_to_json
or a way to override the column names in a ROW
constructor more directly.
Anyway, in general, the principle is that where you want to create a json object with columns a, b, c
, and you wish you could just write the illegal syntax:
ROW(a, b, c) AS outername(name1, name2, name3)
you can instead use scalar subqueries returning row-typed values:
(SELECT x FROM (SELECT a AS name1, b AS name2, c AS name3) x) AS outername
Or:
(SELECT x FROM (SELECT a, b, c) AS x(name1, name2, name3)) AS outername
Additionally, keep in mind that you can compose json
values without additional quoting, e.g. if you put the output of a json_agg
within a row_to_json
, the inner json_agg
result won't get quoted as a string, it'll be incorporated directly as json.
e.g. in the arbitrary example:
SELECT row_to_json(
(SELECT x FROM (SELECT
1 AS k1,
2 AS k2,
(SELECT json_agg( (SELECT x FROM (SELECT 1 AS a, 2 AS b) x) )
FROM generate_series(1,2) ) AS k3
) x),
true
);
the output is:
{"k1":1,
"k2":2,
"k3":[{"a":1,"b":2},
{"a":1,"b":2}]}
Note that the json_agg
product, [{"a":1,"b":2}, {"a":1,"b":2}]
, hasn't been escaped again, as text
would be.
This means you can compose json operations to construct rows, you don't always have to create hugely complex PostgreSQL composite types then call row_to_json
on the output.
Related Topics
Oracle Date To_Char('Month Dd, Yyyy') Has Extra Spaces in It
Cannot Select from Update Returning Clause in Postgres
Referencing Current Row in Filter Clause of Window Function
Insert Data and Set Foreign Keys with Postgres
Subtract Hours from the Now() Function
Oracle: Function Based Index Selective Uniqueness
Postgresql Selecting Most Recent Entry for a Given Id
How to Concatenate All Columns in a Select with SQL Server
Execute Sp_Executesql for Select...Into #Table But Can't Select Out Temp Table Data
How to Insert Identity Manually
Why Is Running a Query on SQL Azure So Much Slower