SQL Server JSON_VALUE syntax

Question

I created a SQL Server Table with 25 columns. One of my columns is actually JSON text, stored as nvarchar(max).

Now I need to able to query this JSON column and parse out the various attributes. I have tried applying JSON_VALUE to my column but am doing something wrong; my query runs but returns NULL for all the values.

The JSON itself looks like:

[
 {
  "lineName":"GHjr",
  "pipeDiameter":"12",
  "pipeLength":"52000",
  "pressure":"15",
  "volume":"107"
 },
 {
  "lineName":"Ks3R",
  "pipeDiameter":"9",
  "pipeLength":"40000",
  "pressure":"15",
  "volume":"80"
 }
]

The SQL I am using is:

select
 DOC_ID, LINE_SPECS,
 JSON_VALUE(LINE_SPECS, '$.lineName')     as line_name,
 JSON_VALUE(LINE_SPECS, '$.pipe_Diameter') as diameter
from dbo.MY_TEST_DOCS
where ISJSON(LINE_SPECS) > 0
  and len(LINE_SPECS) > 3

However, my 2 "parsed" columns are returning all NULL. How do I parse the five attributes from this column?

Put the JSON_VALUE in strict mode to see if any errors come up. The default mode is lax and won't produce errors, it will just return NULL. learn.microsoft.com/en-us/sql/relational-databases/json/… Also, $.pipe_Diameter I think should be $.pipeDiameter — SS_DBA
– SS_DBA, Commented Nov 8, 2017 at 21:10
If you remove the square brackets and make your select match the case-sensitive name in the JSON, it will return a value — dfundako
– dfundako, Commented Nov 8, 2017 at 21:12
I'm not sure it will work. You can use JSON_QUERY instead, but still need a name before the square brackets to call. — dfundako
– dfundako, Commented Nov 8, 2017 at 21:19

Community · Accepted Answer · 2020-06-20 09:12:55Z

6

Without the [] ISJSON is returning false
With [] ISJSON retuns true
Without the [] JSON_VALUE returns NULLs
With [] JSON_VALUE returns values

dbfddle.uk has sql server 2016 available....

create table test (LINE_SPECS nvarchar(max));

insert into test values (N'
 {
  "lineName":"GHjr",
  "pipeDiameter":"12",
  "pipeLength":"52000",
  "pressure":"15",
  "volume":"107"
 },
 {
  "lineName":"Ks3R",
  "pipeDiameter":"9",
  "pipeLength":"40000",
  "pressure":"15",
  "volume":"80"
 }
');

select * 
from test
where ISJSON(LINE_SPECS) > 0
;

GO

| LINE_SPECS |
| :--------- |

select
      JSON_VALUE(LINE_SPECS, '$.lineName')     as line_name
    , JSON_VALUE(LINE_SPECS, '$.pipeDiameter') as diameter
from test
;
GO

line_name | diameter
:-------- | :-------
GHjr      | 12

dbfiddle here

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Nov 9, 2017 at 2:37

Paul Maxwell

35.7k4 gold badges39 silver badges55 bronze badges

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

2 Comments

CarCrazyBen Over a year ago

(and others), your answer(s) really helped!! The JSON I was struggling with included the opening and closing brackets. I created a 2nd column, WITHOUT those brackets, and JSON_VALUE worked as expected. Thank you all for your assistance!

Paul Maxwell Over a year ago

@CarCrazyBen could you please "accept" this answer? Stackoverflows with questions that appear unanswered and this makes it harder for other people to locate answers to their similar problems.

Collectives™ on Stack Overflow

SQL Server JSON_VALUE syntax

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related