Extract JSON from a list in one of the columns in Delta table

Question

Extract JSON from a list in one of the columns in Delta table

Kumar, Arun 336

I have a delta table with a column that have JSON data in the form of a list. I am trying to extract through JSON_VALUE, JSON_QUERY etc, but all giving a NULL value as output. For example my query looks like below -

SET ANSI_WARNINGS OFF
SELECT
id 
JSON_QUERY(data, '$.data1') AS test
JSON_VALUE(data,'$.data1[0]') AS test2
FROM
OPENROWSET(
BULK 'abc/int/tran/',
DATA_SOURCE = 'blob',
FORMAT='DELTA',
fieldterminator ='0x0b',
fieldquote = '0x0b'
) as test

The column data has value like below -

[{"data1":293,"data1c":0.2069103293,"data1upc":{"unit_id":"123456","base1":"0.001","base2":"1000",.........

How to extract data1, data1c, base1, base2 etc?

Appreciate your help in advance

Accepted answer

1 additional answer

Your answer

Answer 1

Bhargava-MSFT 31,261 Microsoft Employee Moderator

Hello Kumar, Arun,

If the data stored in your table as a JSON array(but not a JSON object), then you need to set the index of the JSON object in the Json array.

Ex:

SELECT 
    id,
    JSON_VALUE(j.data, '$[0].data1') AS data1,
    JSON_VALUE(j.data, '$[0].data1c') AS data1c,
    JSON_VALUE(j.data, '$[0].data1upc.base1') AS base1,
    JSON_VALUE(j.data, '$[0].data1upc.base2') AS base2

Answer 2

Loren Prifti 0

Hi, try this and see if it works.

CROSS APPLY OPENJSON(test.data) is used to parse the JSON data in each row into a new JSON object.
WITH (data NVARCHAR(MAX) '$') AS j defines the structure of the JSON you're parsing. Here, it's treating each element in the JSON array as a separate JSON object aliased as j.
JSON_VALUE(j.data, '$.data1') and so on are used to extract the values for each key from the JSON object.

SELECT 
  id,
  JSON_VALUE(j.data, '$.data1') AS data1,
  JSON_VALUE(j.data, '$.data1c') AS data1c,
  JSON_VALUE(j.data, '$.data1upc.base1') AS base1,
  JSON_VALUE(j.data, '$.data1upc.base2') AS base2
FROM 
  OPENROWSET(
    BULK 'abc/int/tran/',
    DATA_SOURCE = 'blob',
    FORMAT='DELTA',
    fieldterminator ='0x0b',
    fieldquote = '0x0b'
  ) AS test
CROSS APPLY OPENJSON(test.data) WITH (data NVARCHAR(MAX) '$') AS j

Kumar, Arun 336 Reputation points

2023-12-29T12:00:18.93+00:00

@Loren Prifti Thanks for the response. It did not work, still showing NULL output.
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-01-09T00:55:19.7933333+00:00

Hello Kumar, Arun,

I am checking to see if you had a chance to look into the above response. Please let me know if you have any further questions.
Kumar, Arun 336 Reputation points

2024-01-09T01:47:22.05+00:00
@Bhargava-MSFT Thanks a lot! It worked
Bhargava-MSFT 31,261 Reputation points Microsoft Employee Moderator

2024-01-09T05:00:38.1833333+00:00

Hello Kumar, Arun,

Glad to know the solution worked. If you don't have any further questions, please consider accepting the answer by hitting the Accept answer and up-vote as it helps the community look for answers to similar questions.

Share via

Extract JSON from a list in one of the columns in Delta table

1 additional answer

Your answer