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 us to aggregate information in json objects*.

  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

You will end up with a table with rows similar to this one:

This aggregated information can be extracted with a custom popup iterating over the objects with MustacheJS. First you need to assign the json object to the ul element using {{#json}}, and then iterate it to get the city and population values for each item of the list using and respectively.

<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>

*If you are in the dataset view, a [Object Object] value will be shown in the dataset cells.