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







share|improve this question

























  • This has nothing to do with spatial, or geojson.

    – Evan Carroll
    3 hours ago











  • 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
    2 hours ago













  • I have an ability to save them like Postgis geom::point, can u provide this solution also?

    – delkov
    2 hours ago











  • 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
    2 hours ago
















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







share|improve this question

























  • This has nothing to do with spatial, or geojson.

    – Evan Carroll
    3 hours ago











  • 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
    2 hours ago













  • I have an ability to save them like Postgis geom::point, can u provide this solution also?

    – delkov
    2 hours ago











  • 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
    2 hours ago














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







share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited 2 hours ago









Evan Carroll

31.3k865208




31.3k865208










asked 3 hours ago









delkovdelkov

576




576













  • This has nothing to do with spatial, or geojson.

    – Evan Carroll
    3 hours ago











  • 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
    2 hours ago













  • I have an ability to save them like Postgis geom::point, can u provide this solution also?

    – delkov
    2 hours ago











  • 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
    2 hours ago



















  • This has nothing to do with spatial, or geojson.

    – Evan Carroll
    3 hours ago











  • 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
    2 hours ago













  • I have an ability to save them like Postgis geom::point, can u provide this solution also?

    – delkov
    2 hours ago











  • 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
    2 hours ago

















This has nothing to do with spatial, or geojson.

– Evan Carroll
3 hours ago





This has nothing to do with spatial, or geojson.

– Evan Carroll
3 hours ago













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
2 hours ago







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
2 hours ago















I have an ability to save them like Postgis geom::point, can u provide this solution also?

– delkov
2 hours ago





I have an ability to save them like Postgis geom::point, can u provide this solution also?

– delkov
2 hours ago













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
2 hours ago





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
2 hours ago










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;





share|improve this answer























    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


















    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;





    share|improve this answer




























      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;





      share|improve this answer


























        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;





        share|improve this answer













        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;






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered 2 hours ago









        Evan CarrollEvan Carroll

        31.3k865208




        31.3k865208






























            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














            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





















































            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







            -

            Popular posts from this blog

            Bundesstraße 106

            Image
            Dieser Artikel beschreibt die Bundesstraße 106 in Deutschland. Zur gleichnamigen Straße in Österreich siehe Mölltal Straße. Vorlage:Infobox hochrangige Straße/Wartung/DE-B Bundesstraße 106 in Deutschland Karte Basisdaten Betreiber: Deutschland  Bundesrepublik Deutschland Straßenbeginn: Wismar ( 53° 54′  N , 11° 24′  O 53.900536 11.396875 ) Straßenende: Schwerin ( 53° 36′  N , 11° 24′  O 53.592785 11.404892 ) Gesamtlänge: 40,7 km Bundesland: Mecklenburg-Vorpommern Ausbauzustand: zweistreifig [1] Bundesstraße 106 in Schwerin (Umgehungsstraße) Straßenverlauf Land Mecklenburg-Vorpommern Landkreis Nordwestmecklenburg Ortsumgehung Wismar Wallensteingraben über   Dorf Mecklenburg Wallensteingraben Groß Stieten Bad Kleinen OT Niendorf Bahnstrecke Lübeck–Bad Kleinen Zickhusen Lübstorf Klein Trebbow OT Kirch Stück ...
            Read more

            Verónica Boquete

            Image
            Verónica Boquete im Trikot der Nationalmannschaft (2013) Personalia Name Verónica Boquete Giadans Geburtstag 9. April 1987 Geburtsort Santiago de Compostela, Spanien Größe 159 cm Position Mittelfeld / Sturm Juniorinnen Jahre Station 2001–2005 Xuventú Aguiño Frauen Jahre Station Spiele (Tore) 1 2005–2008 Prainsa Zaragoza 83 (40) 2008–2010 Espanyol Barcelona 53 (30) 2010 Buffalo Flash 9 0 (9) 2010 Chicago Red Stars 3 0 (1) 2010–2011 Espanyol Barcelona 26 (41) 2011 Philadelphia Independence 13 0 (5) 2011 Energija Woronesch 5 0 (1) 2012–2014 Tyresö FF 39 (14) 2014 Portland Thorns FC 15 0 (4) 2014–2015 1. FFC Frankfurt 23 0 (7) 2015–2016 FC Bayern München 9 0 (0) 2016–2018 Paris Saint-Germain 31 0 (8) 2018 Peking BG Phoenix 2019– Utah Royals FC 0 0 (0) Nationalmannschaft Jahre Auswahl ...
            Read more

            Ida-Boy-Ed-Garten

            Image
            Die Lage des Ida-Boy-Ed-Gartens, rot markiert Der Ida-Boy-Ed-Garten Der Ida-Boy-Ed-Garten, Blick von der Wakenitzmauer durch das Tor in der Stadtmauer Der Ida-Boy-Ed-Garten ist eine Straße der Lübecker Altstadt. Inhaltsverzeichnis 1 Lage 2 Geschichte 3 Literatur 4 Weblinks Lage | Der etwa 90 Meter lange Ida-Boy-Ed-Garten befindet sich an der nördlichen Spitze der Altstadtinsel, vor dem Burgtor. Die in einem Bogen durch eine Grünanlage hangaufwärts führende Straße verbindet die Wakenitzmauer mit der Burgtorbrücke. Geschichte | Der heutige Ida-Boy-Ed-Garten wurde ursprünglich als Verlängerung der Wakenitzmauer angelegt. 1903 wurde in den erhaltenen Teil der mittelalterlichen Stadtmauer, an dem die Wakenitzmauer bei der Einmündung der Kaiserstraße begann, ein Tor mit einem großen Bogen für die Durchführung der Straße und zwei kleineren seitlichen Öffnungen für Fußgänger gebrochen, um für die neu errichteten Wohnhäuser in diesem Bereich ...
            Read more