Retrieving a specific JSON array from a JSON array of values

Question

I have a SQL table that contains a column holding JSON data. One of the JSON values looks as follows:

{
    "_id": "5a450f038104ca3cb0ff74b5",
    "index": 3,
    "guid": "20d807c5-bddc-44b9-97fe-fd18af1b6066",
    "isActive": false,
    "balance": "$2,832.38",
    "picture": "http://placehold.it/32x32",
    "age": 23,
    "eyeColor": "brown",
    "firstname": "Genevieve",
    "lastname": "Green",
    "gender": "female",
    "company": "PROFLEX",
    "email": "[email protected]",
    "phone": "+1 (919) 464-2866",
    "address": "107 Clermont Avenue, Rew, California, 4298",
    "about": "Magna pariatur ut enim nulla pariatur ad Lorem amet. Proident nulla exercitation id Lorem commodo minim cillum irure exercitation labore nostrud nostrud sint. Voluptate commodo ea commodo quis Lorem laborum culpa voluptate enim nulla enim duis.\r\n",
    "registered": "2016-02-16T09:51:25 +05:00",
    "latitude": -16.492643,
    "longitude": -71.782118,
    "tags": [
      "in",
      "non",
      "eiusmod",
      "labore",
      "dolor",
      "laboris",
      "ullamco"
    ],
    "friends": [
      {
        "id": 0,
        "name": "Mccoy Berg",
        "interests": [
          "Music",
          "Birding",
          "Chess"
        ]
      },
      {
        "id": 1,
        "name": "Chase Mcfadden",
        "interests": [
          "Software",
          "Chess",
          "History"
        ]
      },
      {
        "id": 2,
        "name": "Michele Dodson",
        "interests": [
          "Football",
          "Birding",
          "Movies"
        ]
      }
    ],
    "greeting": "Hello, Genevieve! You have 2 unread messages.",
    "favoriteFruit": "strawberry"
  }

I can execute a query that retrieves the first and last name and all the friends as follows:

SELECT
  JSON_VALUE(JsonValue, '$.firstname') as FirstName,
  JSON_VALUE(JsonValue, '$.lastname') as LastName,
  JSON_QUERY(JsonValue, '$.friends') as FriendsList,
From <MyTable>
Where JSON_VALUE(JsonValue,'$.lastname') = 'Green'

The query, as written, returns a JSON string for FriendsList that looks like this:

[  
   {  
      "id":0,
      "name":"Mccoy Berg",
      "interests":[  
         "Music",
         "Birding",
         "Chess"
      ]
   },
   {  
      "id":1,
      "name":"Chase Mcfadden",
      "interests":[  
         "Software",
         "Chess",
         "History"
      ]
   },
   {  
      "id":2,
      "name":"Michele Dodson",
      "interests":[  
         "Football",
         "Birding",
         "Movies"
      ]
   }
]

What I would actually like is just an array of friends names, something like this:

["Mccoy Berg", "Chase Mcfadden", ...]

I'm sure this is possible but my knowledge of JSON is limited.

What happens if you do JSON_QUERY(JsonValue, '$.friends.name')? — DavidG
– DavidG, Commented Dec 28, 2017 at 16:34
Would this be sufficient? It will give you the names in rows: SELECT [Name] FROM OPENJSON(@json, '$.friends') WITH ([Name] NVARCHAR(25) '$.name') — DavidG
– DavidG, Commented Dec 28, 2017 at 16:51
@DavidG that's one piece of the solution, yes. But the OP wants to then convert that result set into a JSON list array, and include the FirstName and LastName columns from the original query. — digital.aaron
– digital.aaron, Commented Dec 28, 2017 at 17:03

Martin Smith · Accepted Answer · 2017-12-28 17:10:28Z

3

SQL server is kind of funny when it comes to creating arrays in the format you are expecting. By default, it always creates JSON arrays as key:value pairs. You can work around this by using STUFF() and FOR XML when working with JSON.

You first create a subquery that returns only the names, then you can STUFF those names into the FriendsList field, like so:

SELECT
FirstName    = JSON_VALUE(JsonValue, '$.firstname')
,LastName    = JSON_VALUE(JsonValue, '$.lastname')
,FriendsList = '[' + STUFF(
                        (SELECT ',' + '"' + [name] + '"'
                            FROM OPENJSON(JsonValue,'$.friends')
                            WITH ( [name] NVARCHAR(100) '$.name') 
                        FOR XML PATH (''))
                        , 1, 1, '') 
            + ']'
FROM <MyTable>

edited Dec 28, 2017 at 17:10

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

answered Dec 28, 2017 at 16:59

digital.aaron

5,7152 gold badges27 silver badges46 bronze badges

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

1 Comment

Martin Smith Over a year ago

Or potentially

FriendsList = '[' + (SELECT STRING_AGG ( '"' + [name] + '"', ',' ) 					 FROM OPENJSON(JsonValue,'$.friends')                      WITH ( [name] NVARCHAR(100) '$.name') )             + ']'

dependant on what version the OP is on

Collectives™ on Stack Overflow

Retrieving a specific JSON array from a JSON array of values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related