SQL Server - transforming a string into JSON Object in routine

Question

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.

What version of SQL are you on? As of 2016 there's native JSON support (much like the native XML support). — Russell Fox
– Russell Fox, Commented Oct 25, 2018 at 0:01
@RussellFox unfortunately, it's not 2016. I've tried using the JSON_QUERY, and it is not recognized — user3334871
– user3334871, Commented Oct 25, 2018 at 0:21

parliamentowl · Accepted Answer · 2018-10-26 22:57:23Z

3

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

edited Oct 26, 2018 at 22:57

answered Oct 25, 2018 at 0:13

parliamentowl

3192 silver badges11 bronze badges

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

1 Comment

Nick Over a year ago

While this might answer the authors question, it lacks some explaining words and/or links to documentation. Raw code snippets are not very helpful without some phrases around them. You may also find how to write a good answer very helpful. Please edit your answer - From Review

Russell Fox · Accepted Answer · 2018-10-26 17:56:13Z

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

Collectives™ on Stack Overflow

SQL Server - transforming a string into JSON Object in routine

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related