CODEWITHSUNDEEP
jsonsql-server
user3334871
user3334871

Reputation: 1361

SQL Server - transforming a string into JSON Object in routine

I am currently working on a way to get a distribution list for all users registered to our application. We are using a SQL server to store information, however, due to a move to a non-relational DB schema in the near future, most of our data is stored in one field as a JSON string (It's a VARCHAR(max) field that looks like JSON). When we serve this data back to our Java controllers, we convert the String into a JSON Object. As my question would most likely indicate, the list of users is located in this JSON string. While I know I can just do SELECT JSON_DATA FROM MYTABLE to get all entries of this field, convert it in Java, and get the users field that way, I would be essentially returning a TON of data and throwing away 95% of it.

I was wondering if there is a way in SQL Server to parse a JSON string? Essentially what I want to do is with the following table:

<table style="width:100%" border="1">
  <tr>
    <th>ID</th>
    <th>JSON_DATA</th> 
  </tr>
  <tr>
    <td>1</td>
    <td>{"data":data,"users":["User1", "User2"]}</td> 
  </tr>
  <tr>
    <td>2</td>
    <td>{"data":data2,"users":["User2", "User3"]}</td>
  </tr>
</table>

I want to return from my SQL routine a list of all unique users.

Upvotes: 0

Views: 4587

Answers (2)

parliamentowl
parliamentowl

Reputation: 319

I think this might give you what you need:

Select JSON_QUERY([fieldName], $.users)

Here's a link to check out too: https://learn.microsoft.com/en-us/sql/t-sql/functions/json-query-transact-sql?view=sql-server-2017

Upvotes: 3

Russell Fox
Russell Fox

Reputation: 5435

Without native JSON support, I'm afraid you're looking a good ol' string parsing. This should get you part of the way: it returns a single string with

"User1", "User2", "User2", "User3"

DECLARE @ThisUserString VARCHAR(255)
    , @FullUserString VARCHAR(255) = ''

DECLARE @xmlSTring VARCHAR(MAX) = 
'<table style="width:100%" border="1">
  <tr>
    <th>ID</th>
    <th>JSON_DATA</th> 
  </tr>
  <tr>
    <td>1</td>
    <td>{"data":data,"users":["User1", "User2"]}</td> 
  </tr>
  <tr>
    <td>2</td>
    <td>{"data":data2,"users":["User2", "User3"]}</td>
  </tr>
</table>'

WHILE CHARINDEX('[', @xmlSTring) > 0
BEGIN

    --  Find the next set of users, like ["User1", "User2"]
    SELECT @ThisUserString = 
        SUBSTRING(
            @xmlSTring
            , /*start*/  CHARINDEX('[', @xmlSTring)
            , /*length*/ CHARINDEX(']', @xmlSTring) - CHARINDEX('[', @xmlSTring) + 1
            )

    --  Add them to the list of all users, like "User1", "User2"
    SELECT @FullUserString += ', ' +
        SUBSTRING(
            @xmlSTring
            , /*start*/  CHARINDEX('[', @xmlSTring) + 1
            , /*length*/ CHARINDEX(']', @xmlSTring) - CHARINDEX('[', @xmlSTring) - 1
            )

    --  And remove this set from the string so our WHILE loop will end sometime:
    SET @xmlSTring = REPLACE(@xmlSTring, @ThisUserString, '')

END

SET @FullUserString = RIGHT(@FullUserString, LEN(@FullUserString) - 2) -- remove the initial comma
SELECT @FullUserString

Upvotes: 1

Related Questions