locked
How can we retrieve data with subset data from sql server RRS feed

  • Question

  • User-257070954 posted

    Hi All,
    How can we retrieve data with subset data from sql server?
    I mean, when I take my personal information i want to get list of roles assigned to me in json format?

    For this purpose i am using 3 tables

    1) User

    2 Role

    3 UserRole - Mapping user and role

    Below i am giving expecting result

    [{
    "FirstName": "AAAA",
    "LastName": "PQR",
    "Roles": [
    "RoleA",
    "RoleB",
    "RoleC"
    ]
    }]

     I am using FOR JSON PATH to get as json from sql

    Monday, October 28, 2019 6:39 AM

All replies

  • User-719153870 posted

    Hi binustrat,

    According to your description, i think what you really want is Json.NET. You can add it in your vs Nuget Package Management.

    After you get the data from your database, you just need to set them to a class User and one of its preperties is a string list.

    Please check below demo:

    using Newtonsoft.Json;
    using System;
    using System.Collections.Generic;
    
    class Program
        {
            static void Main(string[] args)
            {
                User user = new User();
                user.FirstName = "John";
                user.LastName = "Doe";
                user.Roles = new List<string>();
                user.Roles.Add("RoleA");
                user.Roles.Add("RoleB");
                user.Roles.Add("RoleC");
    
                string json = JsonConvert.SerializeObject(user);
    
                Console.Write(json);
            }
    
            public class User
            {
                public string FirstName { get; set; }
                public string LastName { get; set; }
                public List<string> Roles { get; set; }
            }
        }

    Here's the result:

    Best Regard,

    Yang Shen

    Monday, October 28, 2019 8:12 AM
  • User-257070954 posted

    Hi Yang,

                  Thanks for your reply. But i am expecting the answer in sql query.

    Monday, October 28, 2019 10:59 AM
  • User452040443 posted

    Hi,

    Try something like this:

    SELECT
        u.FirstName,
        u.LastName,
        json_query(
            '["' +
            (select string_agg(r.RoleName, '", "')
             from [UserRole] as ur
             inner join [Role] as r
                 on r.IdRole = ur.IdRole
             where ur.IdUser = u.IdUser) +
            '"]') as Roles
    FROM [User] as u
    FOR JSON PATH

    Hope this help

    Monday, October 28, 2019 12:55 PM
  • User-257070954 posted

    Hi imapsp,

                   When I implement your query I am getting an error like 'string_agg' is not a recognized built-in function name. How can I solve this issue?

                   

    Tuesday, October 29, 2019 5:16 AM
  • User-719153870 posted

    Hi binustrat,

    Sorry for misunderstood your requirement before.

    binustrat

    [{
    "FirstName": "AAAA",
    "LastName": "PQR",
    "Roles": [
    "RoleA",
    "RoleB",
    "RoleC"
    ]
    }]

    The reason why @imapsp used string_agg is that he wants the create the Json-like format "Roles": ["RoleA","RoleB","RoleC"] manually since it's not the standard Json format which would be like "key" : "value" .

    The error  'STRING_AGG' is not a recognized built-in function name. is because that Visual Studio 2017 ships with SQL Server 2016 for LocalDb by default and therefore it does not support these new functions that exist in SQL 2017.

    You can solve it by upgrade your LocalDb version to SQL Server 2017 Express LocalDb, OR you can use STUFF combine with FOR XML PATH to replace it.

    You can refer to below code which is modified based on @imapsp's code:

    sql:

    create table #User
    (
    UID int identity(1,1),
    FirstName varchar(50),
    LastName varchar(50)
    )
    
    create table #Role
    (
    RID int identity(1,1),
    RoleName varchar(50),
    )
    
    create table #UserRole
    (
    UID int,
    RID int
    )
    
    insert into #User values('AAAA','PQR')
    insert into #User values('BBBB','SCF')
    
    insert into #Role values('RoleA')
    insert into #Role values('RoleB')
    insert into #Role values('RoleC')
    insert into #Role values('RoleD')
    
    insert into #UserRole values(1,1)
    insert into #UserRole values(1,2)
    insert into #UserRole values(1,3)
    insert into #UserRole values(2,1)
    insert into #UserRole values(2,4)
    
    SELECT
        u.FirstName,
        u.LastName,
        json_query(
            '["' +
            (select stuff((select ','+ r.RoleName
             from [#UserRole] as ur
             inner join [#Role] as r
                 on r.RID = ur.RID
             where ur.UID = u.UID for xml path('')),1,1,'')) +
            '"]') as Roles
    FROM [#User] as u
    FOR JSON PATH

    You might need to update some places considering this demo is created without knowing the structure of your data tables.

    Here's the result of this demo:

    [{"FirstName":"AAAA","LastName":"PQR","Roles":["RoleA,RoleB,RoleC"]},{"FirstName":"BBBB","LastName":"SCF","Roles":["RoleA,RoleD"]}]

    You can see that's still not what you expected at first, if you won't accept this solution, you will need to upgrade your sql version.

    Best Regard,

    Yang Shen

    Tuesday, October 29, 2019 6:27 AM