How to generate JSON with nested elements from MySql

Question

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

Why not post TEXT, when the image only contains text? – Luuk Nov 12 '20 at 16:49 — Luuk, Nov 12 '20 at 16:49
@Luuk I tried, but it after paste it was disorganized. – Ramis Nov 12 '20 at 19:54 — Ramis, Nov 12 '20 at 19:54
i do not see any disorganized things .... (see the edit) – Luuk Nov 12 '20 at 20:01 — Luuk, Nov 12 '20 at 20:01

score 2 · Accepted Answer · answered Nov 12 '20 at 16:40

2

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

answered Nov 12 '20 at 16:40

GMB

216,147
25
84
135

How to generate JSON with nested elements from MySql

1 Answers1