CODEWITHSUNDEEP
mysqlsqljson
Luke
Luke

Reputation: 11

MySql query to get child tables as Json column

I have 3 database tables.

In Mysql query I want to get all columns from Users table And Books and Uers_Books as Json data in Mysql query. So one column of result will have Json data.

Please see the sample data and desired results.

Sample Data
=============
Users
------------------
user_id, user_name
------------------
1, User_A
2, User_B
3, User_C

Books
------------------
book_id, book_name
------------------
1, Book_A
2, Book_B
3, Book_C

Users_Books
-----------------------------------
assigned_date, user_id, book_id
-----------------------------------
01-01-2020, 1, 1
02-01-2020, 1, 2
03-01-2020, 1, 3
04-01-2020, 2, 1
05-01-2020, 3, 1
06-01-2020, 4, 1

Sample Output
==============================
user_id, user_name, json_col
------------------------------
1, User_A, (See Json Output below for this row)
2, User_B
3, User_C

[{
    "assigned_date": "01-01-2020",
    "user_id": "1",
    "book_id": "1",
    "book_detail": {
        "book_id": "1",
        "book_name": "Book_A"
    }
}, {
    "assigned_date": "02-01-2020",
    "user_id": "1",
    "book_id": "2",
    "book_detail": {
        "book_id": "2",
        "book_name": "Book_B"
    }
}, {
    "assigned_date": "03-01-2020",
    "user_id": "1",
    "book_id": "3",
    "book_detail": {
        "book_id": "3",
        "book_name": "Book_C"
    }
}]

I don't want to use GROUP_CONCAT. I just want to use the join like the first solution on this page. MySQL One-to-Many to JSON format

Upvotes: 0

Views: 940

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562260

You should use a combination of JSON_OBJECT() and JSON_ARRAYAGG(). Sorry if that's not what you want.

SELECT u.user_id, u.user_name,
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'assigned_date', ub.assigned_date,
      'user_id', ub.user_id,
      'book_id', ub.book_id,
      'book_detail', JSON_OBJECT(
        'book_id', b.book_id,
        'book_name', b.book_name
      )
    )
  ) AS json_col
FROM Users AS u
JOIN Users_Books AS ub USING (user_id)
JOIN Books AS b USING (book_id)
GROUP BY u.user_id

These JSON functions are supported in MySQL 5.7.22 and later. If you are using an older version, you will have to upgrade first.

P.S.: This is not related to your question, but you should store dates in YYYY-MM-DD format in proper DATE columns, not as strings in MM-DD-YYYY format.

Upvotes: 1

Related Questions