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

2020-11-12 16:49:15 +00:00
Commented Nov 12, 2020 at 16:49 — Luuk
– Luuk, Commented Nov 12, 2020 at 16:49
@Luuk I tried, but it after paste it was disorganized.

Ramis
– Ramis

2020-11-12 19:54:54 +00:00
Commented Nov 12, 2020 at 19:54 — Ramis
– Ramis, Commented Nov 12, 2020 at 19:54
i do not see any disorganized things .... (see the edit)

Luuk
– Luuk

2020-11-12 20:01:58 +00:00
Commented Nov 12, 2020 at 20:01 — Luuk
– Luuk, Commented Nov 12, 2020 at 20:01

GMB · Accepted Answer · 2020-11-12 16:40:40Z

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, 2020 at 16:40

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to generate JSON with nested elements from MySql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related