JSON Query in PostgreSQL
2
I have a table:
CREATE TABLE foo (id,lat,lon)
AS VALUES
( 1, 34, 45 ),
( 1, 45, 56 ),
( 2, 56, 67 ),
( 2, 58, 64 );
How can I get JSON like that:
{"1":[[34,45],[45,56]], "2":[[56,67],[58,64]]}
postgresql json
edited Jan 11 at 23:28
Evan Carroll
31.8k967215
asked Jan 11 at 22:46
delkovdelkov
626
add a comment |
2
I have a table:
CREATE TABLE foo (id,lat,lon)
AS VALUES
( 1, 34, 45 ),
( 1, 45, 56 ),
( 2, 56, 67 ),
( 2, 58, 64 );
How can I get JSON like that:
{"1":[[34,45],[45,56]], "2":[[56,67],[58,64]]}
postgresql json
edited Jan 11 at 23:28
Evan Carroll
31.8k967215
asked Jan 11 at 22:46
delkovdelkov
626
This has nothing to do with spatial, or geojson.
– Evan Carroll
Jan 11 at 23:21
The spatial solution is actually much easier, but then you don't store points in a table. You store either lines or MULTIPOINTs. and you can use PostGIS'sST_ToGeoJSON
– Evan Carroll
Jan 11 at 23:29
I have an ability to save them like Postgis geom::point, can u provide this solution also?
– delkov
Jan 11 at 23:36
no, it's a totally different question that requires you store a GEOGRAPHY or GEOMETRY type on the table, install the PostGIS extension, save all your points in a totally different format, and then query the table differently. It would take 25 pages for me to go over that setup and explain what's happening. I do however HIGHLY suggest the PostGIS in Action book if you're looking for direction and you want to learn GIS.
– Evan Carroll
Jan 11 at 23:44
add a comment |
2
2
2
1
I have a table:
CREATE TABLE foo (id,lat,lon)
AS VALUES
( 1, 34, 45 ),
( 1, 45, 56 ),
( 2, 56, 67 ),
( 2, 58, 64 );
How can I get JSON like that:
{"1":[[34,45],[45,56]], "2":[[56,67],[58,64]]}
postgresql json
edited Jan 11 at 23:28
Evan Carroll
31.8k967215
asked Jan 11 at 22:46
delkovdelkov
626
I have a table:
CREATE TABLE foo (id,lat,lon)
AS VALUES
( 1, 34, 45 ),
( 1, 45, 56 ),
( 2, 56, 67 ),
( 2, 58, 64 );
How can I get JSON like that:
{"1":[[34,45],[45,56]], "2":[[56,67],[58,64]]}
postgresql json
postgresql json
edited Jan 11 at 23:28
Evan Carroll
31.8k967215
asked Jan 11 at 22:46
delkovdelkov
626
edited Jan 11 at 23:28
Evan Carroll
31.8k967215
asked Jan 11 at 22:46
delkovdelkov
626
edited Jan 11 at 23:28
Evan Carroll
31.8k967215
edited Jan 11 at 23:28
Evan Carroll
31.8k967215
edited Jan 11 at 23:28
Evan Carroll
31.8k967215
31.8k967215
asked Jan 11 at 22:46
delkovdelkov
626
asked Jan 11 at 22:46
delkovdelkov
626
asked Jan 11 at 22:46
delkovdelkov
626
626
This has nothing to do with spatial, or geojson.
– Evan Carroll
Jan 11 at 23:21
The spatial solution is actually much easier, but then you don't store points in a table. You store either lines or MULTIPOINTs. and you can use PostGIS'sST_ToGeoJSON
– Evan Carroll
Jan 11 at 23:29
I have an ability to save them like Postgis geom::point, can u provide this solution also?
– delkov
Jan 11 at 23:36
no, it's a totally different question that requires you store a GEOGRAPHY or GEOMETRY type on the table, install the PostGIS extension, save all your points in a totally different format, and then query the table differently. It would take 25 pages for me to go over that setup and explain what's happening. I do however HIGHLY suggest the PostGIS in Action book if you're looking for direction and you want to learn GIS.
– Evan Carroll
Jan 11 at 23:44
add a comment |
This has nothing to do with spatial, or geojson.
– Evan Carroll
Jan 11 at 23:21
The spatial solution is actually much easier, but then you don't store points in a table. You store either lines or MULTIPOINTs. and you can use PostGIS'sST_ToGeoJSON
– Evan Carroll
Jan 11 at 23:29
I have an ability to save them like Postgis geom::point, can u provide this solution also?
– delkov
Jan 11 at 23:36
no, it's a totally different question that requires you store a GEOGRAPHY or GEOMETRY type on the table, install the PostGIS extension, save all your points in a totally different format, and then query the table differently. It would take 25 pages for me to go over that setup and explain what's happening. I do however HIGHLY suggest the PostGIS in Action book if you're looking for direction and you want to learn GIS.
– Evan Carroll
Jan 11 at 23:44
This has nothing to do with spatial, or geojson.
– Evan Carroll
Jan 11 at 23:21
This has nothing to do with spatial, or geojson.
– Evan Carroll
Jan 11 at 23:21
The spatial solution is actually much easier, but then you don't store points in a table. You store either lines or MULTIPOINTs. and you can use PostGIS's
ST_ToGeoJSON– Evan Carroll
Jan 11 at 23:29
The spatial solution is actually much easier, but then you don't store points in a table. You store either lines or MULTIPOINTs. and you can use PostGIS's
ST_ToGeoJSON– Evan Carroll
Jan 11 at 23:29
I have an ability to save them like Postgis geom::point, can u provide this solution also?
– delkov
Jan 11 at 23:36
I have an ability to save them like Postgis geom::point, can u provide this solution also?
– delkov
Jan 11 at 23:36
no, it's a totally different question that requires you store a GEOGRAPHY or GEOMETRY type on the table, install the PostGIS extension, save all your points in a totally different format, and then query the table differently. It would take 25 pages for me to go over that setup and explain what's happening. I do however HIGHLY suggest the PostGIS in Action book if you're looking for direction and you want to learn GIS.
– Evan Carroll
Jan 11 at 23:44
no, it's a totally different question that requires you store a GEOGRAPHY or GEOMETRY type on the table, install the PostGIS extension, save all your points in a totally different format, and then query the table differently. It would take 25 pages for me to go over that setup and explain what's happening. I do however HIGHLY suggest the PostGIS in Action book if you're looking for direction and you want to learn GIS.
– Evan Carroll
Jan 11 at 23:44
add a comment |
1 Answer
1
active
oldest
votes
4
First you do something like this,
SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
FROM foo
GROUP BY id;
That aggregates the values into an JSON Array. You get
id | j
----+----------------------
2 | [[56, 67], [58, 64]]
1 | [[34, 45], [45, 56]]
From there you need to build an JSON Object..
SELECT jsonb_object_agg(id,j)
FROM (
SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
FROM foo
GROUP BY id
) AS t;
answered Jan 11 at 23:28
Evan CarrollEvan Carroll
31.8k967215
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f226956%2fjson-query-in-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
4
First you do something like this,
SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
FROM foo
GROUP BY id;
That aggregates the values into an JSON Array. You get
id | j
----+----------------------
2 | [[56, 67], [58, 64]]
1 | [[34, 45], [45, 56]]
From there you need to build an JSON Object..
SELECT jsonb_object_agg(id,j)
FROM (
SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
FROM foo
GROUP BY id
) AS t;
answered Jan 11 at 23:28
Evan CarrollEvan Carroll
31.8k967215
add a comment |
4
First you do something like this,
SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
FROM foo
GROUP BY id;
That aggregates the values into an JSON Array. You get
id | j
----+----------------------
2 | [[56, 67], [58, 64]]
1 | [[34, 45], [45, 56]]
From there you need to build an JSON Object..
SELECT jsonb_object_agg(id,j)
FROM (
SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
FROM foo
GROUP BY id
) AS t;
answered Jan 11 at 23:28
Evan CarrollEvan Carroll
31.8k967215
add a comment |
4
4
4
First you do something like this,
SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
FROM foo
GROUP BY id;
That aggregates the values into an JSON Array. You get
id | j
----+----------------------
2 | [[56, 67], [58, 64]]
1 | [[34, 45], [45, 56]]
From there you need to build an JSON Object..
SELECT jsonb_object_agg(id,j)
FROM (
SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
FROM foo
GROUP BY id
) AS t;
answered Jan 11 at 23:28
Evan CarrollEvan Carroll
31.8k967215
First you do something like this,
SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
FROM foo
GROUP BY id;
That aggregates the values into an JSON Array. You get
id | j
----+----------------------
2 | [[56, 67], [58, 64]]
1 | [[34, 45], [45, 56]]
From there you need to build an JSON Object..
SELECT jsonb_object_agg(id,j)
FROM (
SELECT id, jsonb_agg(jsonb_build_array(lat,lon)) AS j
FROM foo
GROUP BY id
) AS t;
answered Jan 11 at 23:28
Evan CarrollEvan Carroll
31.8k967215
answered Jan 11 at 23:28
Evan CarrollEvan Carroll
31.8k967215
answered Jan 11 at 23:28
Evan CarrollEvan Carroll
31.8k967215
answered Jan 11 at 23:28
Evan CarrollEvan Carroll
31.8k967215
31.8k967215
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f226956%2fjson-query-in-postgresql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
This has nothing to do with spatial, or geojson.
– Evan Carroll
Jan 11 at 23:21
The spatial solution is actually much easier, but then you don't store points in a table. You store either lines or MULTIPOINTs. and you can use PostGIS's
ST_ToGeoJSON– Evan Carroll
Jan 11 at 23:29
I have an ability to save them like Postgis geom::point, can u provide this solution also?
– delkov
Jan 11 at 23:36
no, it's a totally different question that requires you store a GEOGRAPHY or GEOMETRY type on the table, install the PostGIS extension, save all your points in a totally different format, and then query the table differently. It would take 25 pages for me to go over that setup and explain what's happening. I do however HIGHLY suggest the PostGIS in Action book if you're looking for direction and you want to learn GIS.
– Evan Carroll
Jan 11 at 23:44