How to set more than a filter over JSON data using JSON_VALUE?

Question

I'm just trying to set a query to get data from a collection of object JSON:

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"
 }
');

Now, as getting lineName of the first object ( lineName : Ghjr) is a success

    select
      JSON_VALUE(LINE_SPECS, '$.lineName')     as line_name
    , JSON_VALUE(LINE_SPECS, '$.pipeDiameter') as diameter

from test
WHERE JSON_VALUE(LINE_SPECS, '$.lineName') = 'GHjr' 
;

that is not possible when I try to get the second that is "Ks3R" :

    select
      JSON_VALUE(LINE_SPECS, '$.lineName')     as line_name
    , JSON_VALUE(LINE_SPECS, '$.pipeDiameter') as diameter

from test
WHERE JSON_VALUE(LINE_SPECS, '$.lineName') = 'Ks3R'

How can I do that ? Thanks.

D-Shih · Accepted Answer · 2018-10-21 14:34:39Z

4

First your JSON data isn't valid, it might be an array.

look like this.

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"
 }
]');

You can try to use OPENJSON with CROSS APPLY to parse JSON and make it.

select
     t2.*
from test t1 
CROSS APPLY 
    OPENJSON(t1.LINE_SPECS)
    WITH
        (
            line_name varchar(MAX) N'$.lineName',
            diameter varchar(MAX) N'$.pipeDiameter'
        ) AS t2
WHERE line_name  = 'Ks3R'

sqlfiddle

answered Oct 21, 2018 at 14:34

D-Shih

46.5k6 gold badges38 silver badges56 bronze badges

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

3 Comments

Gordon Linoff Over a year ago

really good catch on the malformed JSON. I wish I could upvote twice, once for getting the data right and once for getting the solution right ;)

D-Shih Over a year ago

@GordonLinoff Thanks :)

Riddick Over a year ago

Thank you so much! You made my day.

Collectives™ on Stack Overflow

How to set more than a filter over JSON data using JSON_VALUE?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related