SQL Server 2008 JSON_VALUE with replace method

Question

In SQL Server 2008 (dataset from SQL Azure) I have a query where I am attempting to parse multiple elements from an existing JSON formatted column.

JSON_VALUE with the replace method worked fine for the first few columns.

However, SQL Server is throwing an error now after adding an additional JSON_VALUE using the replace method.

This is my query:

SELECT 
    ApplicationID, Type, CreateDate,
    JSON_VALUE(RawContent,'$.errorName') AS ErrorName,
    JSON_VALUE(replace(replace(RawContent,'[',''),']',''),'$.errorMessage') AS ErrorMessage,
    JSON_VALUE(RawContent,'$.bookId') AS BookId,
    JSON_QUERY(RawContent,'$.urlParams') AS UrlParams,
    JSON_VALUE(replace(RawContent,',',''),'$.userAgent') AS UserAgent, --line in question
    JSON_VALUE(replace(RawContent,' ','') ,'$.stackTrace') AS StackTrace
FROM 
    dbo.Feedback
WHERE 
    ISJSON(RawContent) > 0 
ORDER BY 
    RecordID

My json formatted column (RawContent) looks like this:

{"errorName": "not authorized",
 "errorMessage":"Request denied: account permission error",
 "bookId":"150675",
 "urlParams":{"audiobook":"https://node.axisnow.com/#audiobook/"},
 "userAgent":"Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36"}

So I'm expecting the query to produce a column for each JSON_VALUE, while removing the comma ',' from within the column "userAgent" as just:

Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/60.0.3112.113 Safari/537.36

only without the comma "," to look like:(KHTML like Gecko)

The replace method works for errormessage, and stack trace, but for some reason it causes an error when I run it on the userAgent line, it seems to not like the "," character and I was wondering what I need to use as I can't find anything online.

Error msg:

Msg 13609, Level 16, State 2, Line 1
JSON text is not properly formatted. Unexpected character ':' is found at position 43.

Please help. My boss was expecting this to be done yesterday...

All those JSON functions are new features in SQL Server 2016 - I wonder how you expect them to work in SQL Server 2008...... — marc_s
– marc_s, Commented Sep 21, 2017 at 18:24
Are you maybe running SSMS 2008 (the management GUI), but against a 2016 engine? Run SELECT @@VERSION and see what engine version your coding against. Again: SQL Server 2008 never had anything like JSON_VALUE - this cannot possibly work on 2008 (unless you've installed some kind of a .NET / SQL-CLR based extension or something). — marc_s
– marc_s, Commented Sep 21, 2017 at 18:48
@marc_s the JSON_VALUE works because the dataset comes from SQL azure. ill edit the question to reflect this. thanks — Duan Walker
– Duan Walker, Commented Sep 21, 2017 at 18:53

Duan Walker · Accepted Answer · 2017-09-22 18:20:03Z

1

based on my research, there appears to be no ideal solution for this task. as the JSON_VALUE method uses the comma as a delimiter to break down the json string into multiple columns, it cant be removed in this instance. hope this helps someone else

answered Sep 22, 2017 at 18:20

Duan Walker

3151 silver badge11 bronze badges

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

Collectives™ on Stack Overflow

SQL Server 2008 JSON_VALUE with replace method

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related