Question 402292 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL – Is it possible to create a query contains a table with a column containing an array from another table?

Is it possible to create a query containing the profiles (table: profiles) with another column containing an array of all the modifications made to the profiles (table: profiles_log)?

profiles structure

table profiles

table profiles_log

I want a query with this result :

my goal

I want to build an html table of profiles with an extension possibility to see the logs under each profile

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I use winnmp to host my data locally to do my tests. Is it normal that it doesn't work?

    result


  2. 0

    USE JSON_ARRAYAGG() to get the timestamp & log value from profiles_log group by profiles_id.

    Then join with profiles table and get data respectively as follows:

    WITH T1 AS (
    SELECT 
        profiles_id,
        JSON_ARRAYAGG(
            JSON_OBJECT(
                'timestamp', timestamp,
                'log', log
            )
        ) AS array_col
    FROM profiles_log
    GROUP BY profiles_id
)
SELECT profiles.*,
    CAST(T1.array_col AS JSON) AS array_col
FROM        profiles
INNER JOIN  T1
ON profiles.id = T1.profiles_id;

    Live query: db<>fiddle

    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search