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": "genevievegreen@proflex.com",
    "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, Dec 28 '17 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, Dec 28 '17 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, Dec 28 '17 at 17:03

score 3 · Accepted Answer · edited Dec 28 '17 at 17:10

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>

Or potentially `FriendsList = '[' + (SELECT STRING_AGG ( '"' + [name] + '"', ',' ) FROM OPENJSON(JsonValue,'$.friends') WITH ( [name] NVARCHAR(100) '$.name') ) + ']'` dependant on what version the OP is on — Martin Smith, Dec 28 '17 at 17:13

Retrieving a specific JSON array from a JSON array of values

1 Answers1