1

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.

Thom A
  • 88,727
  • 11
  • 45
  • 75
sinDizzy
  • 1,300
  • 7
  • 28
  • 60
  • While asking a question, you need to provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example): (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). All within the question as text, no images. – Yitzhak Khabinsky Mar 24 '23 at 20:08

2 Answers2

1

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
Yitzhak Khabinsky
  • 18,471
  • 2
  • 15
  • 21
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

SelVazi
  • 10,028
  • 2
  • 13
  • 29