Get column value as array list for distinct attribute

Question

I am trying to write a query to get this result:

[{
    "server_name": "windows",
    "app_names": ["firebase", "news api", "video api"]
}, {
    "server_name": "linux",
    "app_names": ["game"]
}]

However this is the result I am getting:

[{
    "server_name": "windows",
    "app_names": ["firebase", "news api", "video api", "game"]
}, {
    "server_name": "linux",
    "app_names": ["firebase", "news api", "video api", "game"]
}]

Database tables:

A
server_id | server_name
-----------------------
1           windows
2           Linux


B
app_id | app_name
-----------------------
1        firebase
2        news api
3        video api
4        game


C
status_id | status  | server_id | app_id
----------------------------------------
1           UP          1           1
2           DOWN        1           2
3           DOWN        1           3
4           DOWN        2           4

Query that I have:

SET @output = (
    SELECT DISTINCT A.server_name,
        JSON_QUERY(REPLACE(REPLACE(( SELECT
            B.app_name
            FROM B
                INNER JOIN C ON
                    B.app_id = C.app_id
                INNER JOIN A ON
                    C.server_id = A.server_id
            FOR 
                JSON AUTO
            ), N'{"app_name":', N''),'"}', '"'
        )) AS [app_names]
    FROM
        A
    FOR JSON PATH
)

I am close to the result that I want, but don't know what I am missing out on. All the app_names appear no matter what I do.

Gordon Linoff · Accepted Answer · 2020-07-02 01:19:27Z

1

Remove the A from the subquery so you have a correlated subquery:

SET @output = (
    SELECT DISTINCT A.server_name,
            JSON_QUERY(REPLACE(REPLACE(( SELECT B.app_name
                                         FROM B JOIN
                                              C
                                              ON B.app_id = C.app_id
                                         WHERE C.server_id = A.server_id
                                         FOR JSON AUTO
                                       ), N'{"app_name":', N''),'"}', '"'
                     )) AS [app_names]
    FROM A
    FOR JSON PATH
   )

answered Jul 2, 2020 at 1:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

Jam1 Over a year ago

Why it did not work in the JOIN clause and succeeded in the WHERE?

Gordon Linoff Over a year ago

@Jam1 . . . "work"? Well it does "work". It just doesn't do what you want. You can see the difference. With A in the subquery, it returns all the apps on every row.

Jam1 Over a year ago

Lol, you are right, what I meant as "work" was the intended result

Collectives™ on Stack Overflow

Get column value as array list for distinct attribute

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related