Question 109044 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Extract value from a JSON array with no name

I have a table with a record that has JSON content and it is an array with no name. Sample data looks like this:

carId carType parts
Z003 Company [{"Value":"New","Type":"Brakes","Code":"D"},{"Value":"Upgraded","Type":"Doors","Code":"E1"}]
Z003 Company [{"Value":null,"Type":"Brakes","Code":"D"},{"Value":null,"Type":"Doors","Code":"E1"}]
Z003 Company [{"Value":"USed","Type":"Brakes","Code":"D"},{"Value":"New","Type":"Tires","Code":"G7"}]

There are actually about 20 to 30 classes within the array but i have shown only 2 for simplicity. So for each record I am trying to extract certain classes with certain attributes into their own field. Like so:

carId carType BrakesCode DoorsValue
Z003 Company D Upgraded
Z003 Company D null
Z003 Company D null

I have tried numerous queries and have not succeeded. Here is my latest:

SELECT carId, carType, JSON_VALUE( JSON_QUERY(parts,'$[0]'),'$[0].Code')
FROM [Assets].[dbo].[Cars]

Which results in:

JSON text is not properly formatted. Unexpected character ‘.’ is found at position 4.

I know I have to insert a WHERE somewhere so I can match the Type=Brakes but am unsure where that goes. Any help appreciated.

Tags:

2

Answers


  1. 0

    we can use OPENJSON to parses our JSON text and returns objects and properties from the JSON input as rows and columns, then we use MAX() With CASE to get the expected data,

    with cte as (
  SELECT carId, carType, Code, Type, value
  FROM Cars
  CROSS APPLY OPENJSON([parts], '$')
  WITH (
    Code NVARCHAR(50) '$.Code',
    Type NVARCHAR(50) '$.Type',
    value NVARCHAR(50) '$.Value'
  )
)
select carId, carType,
  MAX(case when Type = 'Brakes' then Code end) as BrakesCode,
  MAX(case when Type = 'Doors' then [Value] end) as DoorsValue
from cte
where Type in ('Brakes', 'Doors')
group by carId, carType

    Result :

    carId   carType BrakesCode  DoorsValue
Z001    Company D           Upgraded
Z002    Company D           null
Z003    Company D           null

    Demo here

    Login or Signup to reply.
  2. 0

    One more method.

    SQL

    -- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, carId CHAR(4), carType VARCHAR(20), parts NVARCHAR(MAX));
INSERT INTO @tbl (carId, carType, parts) VALUES
('Z003', 'Company', '[{"Value":"New","Type":"Brakes","Code":"D"},{"Value":"Upgraded","Type":"Doors","Code":"E1"}]'),
('Z003', 'Company', '[{"Value":null,"Type":"Brakes","Code":"D"},{"Value":null,"Type":"Doors","Code":"E1"}]'),
('Z003', 'Company', '[{"Value":"USed","Type":"Brakes","Code":"D"},{"Value":"New","Type":"Tires","Code":"G7"}]');
-- DDL and sample data population, end

;WITH brakes AS
(
    SELECT t.ID
        , BrakesCode = JSON_VALUE(j.value,'$.Code')
    FROM @tbl AS t
       CROSS APPLY OPENJSON(parts) AS j
       CROSS APPLY OPENJSON(j.value) AS j2
    WHERE j2.[key] = 'Type'
        AND j2.value = 'Brakes'
)
, doors AS
(
    SELECT t.ID
        , DoorsValue = JSON_VALUE(j.value,'$.Value')
    FROM @tbl AS t
       CROSS APPLY OPENJSON(parts) AS j
       CROSS APPLY OPENJSON(j.value) AS j2
    WHERE j2.[key] = 'Type'
        AND j2.value = 'Doors'
)
SELECT t.ID, t.carId, t.carType, BrakesCode, DoorsValue
FROM @tbl AS t 
    LEFT OUTER JOIN brakes ON t.ID = brakes.ID
    LEFT OUTER JOIN doors ON t.ID = doors.ID;

    Output

    ID carId carType BrakesCode DoorsValue
    1 Z003 Company D Upgraded
    2 Z003 Company D NULL
    3 Z003 Company D NULL
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search