SQLize Online / PHPize Online

Articles    Popular

How to aggregate data in JSON format

Tags: PostgreSQL SQL Server 2017 MySQL 5.x MySQL 8.x

Data:

Table: clients
id name
1Client One
2Client Two
3Client Three
Table: client_phonenumbers
id client_id phone_type number
11home+1 (012) 34567
21work+1 (012) 53219
32home+1 (012) 26974

Question: What can be used to receive JSON aggregated data from two tables?

Solution:

Tags: PostgreSQL

The way to acheive desired result in PostgreSQL - using ROW_TO_JSON for jenerate JSON object from whole row data and JSON_AGG for aggregate rows to JSON array

  Use this link for 
    PostgreSQL fiddle code test
  

Tags: MySQL 5.x MySQL 8.x

In MySQL we canuse JSON_OBJECT for jenerate JSON object from named fields data and JSON_ARRAYAGG for aggregate rows to JSON array

  Use this link for 
    MySQL fiddle code test

  

Tags: SQL Server 2017

Modern SQL Server versions provide "magic" FOR JSON AUTO that generate JSON output without any data manipulation

  Use this link for 
    MS SQL 2017 fiddle code test
  


If you find an error on this page, please contact us via feedback form