Nested JSON using ms sql server

Question

I have a table with USER_DATA (user data table) with 2 rows (2 entries basically)

I created one nested JSON query ->

SELECT CONCAT( first_name,' ', last_name) AS displayName, 
       first_name AS givenName, last_name AS surname,
       identities = (SELECT login_name AS issuerAssignedId 
                       FROM user_data 
                        FOR JSON AUTO) 
 FROM user_data
  FOR JSON PATH, ROOT('users');

Here, I am getting this output ->

{
  "users": [
    {
      "displayName": "David Dave",
      "givenName": "David",
      "surname": "Dave",
      "identities": [
        {
          "issuerAssignedId": "System"
        },
        {
          "issuerAssignedId": "Administrators"
        }       
      ]
    },
    {
      "displayName": "Tony Padila",
      "givenName": "Tony",
      "surname": "Padila",
      "identities": [
        {
          "issuerAssignedId": "System"
        },
        {
          "issuerAssignedId": "Administrators"
        }
      ]
    }

But the problem is -> inside identities,

"issuerAssignedId": "System" ----> Belongs to Dave

"issuerAssignedId": "Administrators" ----> Belongs to Tony

But I am not able to stop the inner select query (Not able to map correctly)

The correct output should be --->

{
      "users": [
        {
          "displayName": "David Dave",
          "givenName": "David",
          "surname": "Dave",
          "identities": [
            {
              "issuerAssignedId": "System"
            }      
          ]
        },
        {
          "displayName": "Tony Padila",
          "givenName": "Tony",
          "surname": "Padila",
          "identities": [
            {
              "issuerAssignedId": "Administrators"
            }
          ]
        }

PLEASE HELP.

Is there any relation ship between table USER_DATE and USER_DATA ? You are getting all the records from second table select LOGIN_NAME AS issuerAssignedId **from** USER_DATE for JSON AUTO try to add a condition to get only the records that you need — Sowmyadhar Gourishetty
– Sowmyadhar Gourishetty, Commented Aug 11, 2020 at 10:42
Is there any relation ship between table USER_DATE and USER_DATA -> it is one table. — Sharad esdee Dutta
– Sharad esdee Dutta, Commented Aug 11, 2020 at 10:49
I have a column in user data as Login_Name -> which I want to rename as "issuerAssignedId", create a nested JSON for it and then whatever value that column has, will be saved in "identifiers" — Sharad esdee Dutta
– Sharad esdee Dutta, Commented Aug 11, 2020 at 10:50

Sowmyadhar Gourishetty · Accepted Answer · 2020-08-11 11:01:35Z

2

You are missing the condition in the inner query and why do you want the identities to be a separate array in the JSON output.

I have updated the query as per my understanding please refer below sql, I'm not sure why you are having the ** in the query

     SELECT CONCAT (
        FIRST_NAME
        ,' '
        ,LAST_NAME
        ) AS displayName
    ,FIRST_NAME AS givenName
    ,LAST_NAME AS surname
    ,identities = (
        SELECT innr.LOGIN_NAME AS issuerAssignedId
        FROM USER_DATA
        innr
        WHERE
        innr.LOGIN_NAME = ottr.LOGIN_NAME
        FOR JSON AUTO
        )
   FROM USER_DATA ottr
   FOR JSON PATH
      ,ROOT('users');

edited Aug 11, 2020 at 11:01

answered Aug 11, 2020 at 10:57

Sowmyadhar Gourishetty

1,8981 gold badge10 silver badges15 bronze badges

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

6 Comments

Sharad esdee Dutta Over a year ago

** is nothing but (BOLD) in stackoverflow, if you want to BOLD specific work, put ** in front and back or just select the word and press ctrl + b

Sharad esdee Dutta Over a year ago

let me the query try once

Sowmyadhar Gourishetty Over a year ago

I have used INNER and OUTER previously which are the key words, updated the alias names now. Please try now once

Sharad esdee Dutta Over a year ago

like -> Incorrect Syntax near WHERE (first "where" you added) followed by incorrect syntax near INNER (first "inner" which you added), same errors for others additions too

Sowmyadhar Gourishetty Over a year ago

Thank you, Glad your issue is fixed.

|

Collectives™ on Stack Overflow

Nested JSON using ms sql server

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related