azure-data-factoryazure-cosmosdb

reading cosmos json document into 1 column in a table with ADF

I wanna read cosmos-documents in ADF with copy data. I know how to map to different columns. I would like to put the entire document into 1 column. Its easy to read a nested array into 1 column through mapping but is it possible to map the entire document into 1 column?

sample json data "id": "12345-6789", "stamp": "2024-09-10 09:34", "nested" [ { "year": 2024, "month": 09, "value": 10 }, { "year": 2024, "month": 08, "value": 5 } ] I need to use copy data or data flow to copy that into a single column i an ms sql table so the contents of the table has the entire json document. I know how to select an array inside a jsondocument with cosmos query into a single mapping. But how to get the entire document

Solution

  • is it possible to map the entire document into 1 column?

    You cannot directly map the entire document into single column you need use the openjson table-valued function that parses JSON text follow the below approach.

    enter image description here

    @string(activity('Lookup1').output.value)

    enter image description here

    declare @json nvarchar(4000)=N'@{variables('Json')}';
INSERT INTO test_tgt
SELECT * FROM OPENJSON(@json)
WITH (
col1 nvarchar(max) '$' AS JSON
);

    This script will insert the data into the table test_tgt which is already created.

    enter image description here

    SQL table output

    enter image description here