CODEWITHSUNDEEP
mysqlsqlaggregate-functionsmysql-json
Ramis
Ramis

Reputation: 16539

How to generate JSON with nested elements from MySql

I have table like this:

 +----+-------+-------+--------------+ 
 | id | title | city  | street       |  
 +----+-------+-------+--------------+ 
 | 1  | First | London|  Oxford      |
 +----+-------+-------+--------------+ 
 |  2 | Second| Berlin| Nievenheimer |      
 +----+-------+-------+--------------+

Is here a way to write MySql query which will generate JSON output with nested elements. Similar like this:

{
  1: {
    "title": "First",
    "address": {
      "city": "London",
      "street": "Oxford"
    }
  },
  2: {
    "title": "Second",
    "address": {
      "city": "Berlin",
      "street": "Nievenheimer"
    }
  }
}

Upvotes: 0

Views: 40

Answers (1)

GMB
GMB

Reputation: 222432

You can use json generation functions:

select json_object_agg(
    id,
    json_object(
        'title',   title,
        'address', json_object('city', city, 'street', street)
    )
) res
from mytable

Upvotes: 2

Related Questions