This content applies to a previous version of CARTO

In October 2021 we released a new version of our platform. You can find the latest documentation at docs.carto.com

Back to Help Center

How to Convert Arrays to JSON Objects

Learn how to convert arrays to json objects to display their data in Builder popups.

CARTO supports json (and jsonb) data types. The json_agg PostgreSQL function allows aggregating information in json objects.

SQL Query Example

SELECT wb.cartodb_id,
       wb.name as country,
       wb.the_geom,
       json_agg(
           row_to_json(
               ( SELECT r
                   FROM ( SELECT pp.name as city,
                                pp.pop_max as population
                        ) r
               ),
               true
           )
       ) as json
  FROM world_borders wb
  JOIN populated_places pp
    ON wb.name = pp.adm0name
GROUP BY wb.cartodb_id

Result Table

The query produces rows with aggregated JSON data:

cartodb_idthe_geomcountryjson
166PolygonPortugal[{“city”:“Coimbra”,“population”:106582},{“city”:“Aveiro”,“population”:54162},…]

Custom Popup Implementation

Display this aggregated data using MustacheJS in a custom popup. Assign the json object using {{#json}} and iterate through items to extract city and population values:

<div class="cartodb-popup header blue v2">
  <a href="#close" class="cartodb-popup-close-button close">x</a>
  <div class="cartodb-popup-header">
    <h1></h1>
    <span class="separator"></span>
  </div>
  <div class="cartodb-popup-content-wrapper">
    <div class="cartodb-popup-content">
      <h4>Cities</h4>
      <ul style="list-style-type: disc; list-style-position: inside; ">
      {{#json}}
      <li>
        <ul style="margin-left:5px;list-style-type: circle; list-style-position: inside; ">
          <li>Population: </li>
        </ul>
      </li>
      {{/json}}
      </ul>
    </div>
  </div>
  <div class="cartodb-popup-tip-container">
  </div>
</div>

Note: In dataset view, [Object Object] values will display in cells for json fields.