How to concat a json arrays objects values in SQLite

As part of a larger select query I need to extract the values from an json array's objects as a comma separated string.

I have managed to get the json array out of the json object:

SELECT * FROM (SELECT json_extract(Site.Login, '$.Uris') FROM Site);

Second variant giving the same result:

SELECT value FROM json_each(Site.Login), Site WHERE json_each.key = 'Uris';

A test with a single row giving the wanted result:

SELECT group_concat(json_extract(value, '$.Uri')) as login_uri FROM json_each('[{"Uri":"https://cnn.com"},{"Uri":"https://bbc.com"},{"Uri":"https://reuters.com"}]');

I am lost in the matrix. I have tried to various ways of combining the query code above, but I can not make any headway.

An example of a Site.Login cell. The number of Uri objects can be from 0 to infinite.

{
    "Uris": [
            {"Uri":"https://cnn.com"},
            {"Uri":"https://bbc.com"},
            {"Uri":"https://reuters.com"}
    ],
    "Username": "ghhhhhhhhhhhhhfgggggggggggggggg",
    "Password": "hgfhfghfghfgh",
    "PasswordRevisionDate": "2019-01-07T21:51:42.65Z",
    "Totp": "gffffffffffffffffffffffhhhhhhhhhhhhhhhfghghgfh",
    "Name": "hgfhfghfghfghfgh",
    "PasswordHistory": [
        {
            "Password": "ghfghfghfghfghfg",
            "LastUsedDate": "2019-01-07T21:51:42.65Z"
        }
    ]
}

The full layout of the Site table:

CREATE TABLE "Site" (
"Id" varchar primary key not null ,
"FolderId" varchar ,
"UserId" varchar ,
"OrganizationId" varchar ,
"Name" varchar ,
"Notes" varchar ,
"Fields" varchar ,
"PasswordHistory" varchar ,
"Login" varchar ,
"Card" varchar ,
"Identity" varchar ,
"SecureNote" varchar ,
"Favorite" integer ,
"Edit" integer ,
"OrganizationUseTotp" integer ,
"RevisionDateTime" bigint ,
"Type" integer ,
"Data" varchar )

The select query should return a column named login_uri containing the extracted json array objects values as a concatenated string: https://cnn.com,https://bbc.com,https://reuters.com

Solution

You're very close!

SELECT group_concat(json_extract(j.value, '$.Uri')) AS login_uri
FROM site AS s
JOIN json_each(json_extract(s.login, '$.Uris')) AS j

gives

login_uri                                          
---------------------------------------------------
https://cnn.com,https://bbc.com,https://reuters.com

If you want each row in the table to give a result row, instead of all uris in the entire table being concatenated together into a single string, add a GROUP BY s.id to the end.