Translate T-SQL Json query to USQL

Question

Hi I am trying to translate this logic from T-sql json query to usql. But i am not able to achieve the same results. Any help will be appreciated.

select N'{
"rowid": "1",
"freeresponses": {
    "fr1": "1.1",
    "fr2": "1.1",
    "fr3": "1.3",
    "fr4": "1.4",
    "fr5": "1.4"
}
}'as jsontext
into dbo.tmp#


SELECT convert (int, JSON_VALUE(jsontext,'$.rowid' )) as rowid,
c.[key], c.[value]
FROM dbo.tmp#
cross apply openjson(json_query(jsontext,'$.freeresponses') )
as c;

the result will be like this in SQL server.

rowid | key | value

1     |  fr1| 1.1

1     |  fr2| 1.1

1     |  fr3| 1.3

1     | fr4 | 1.4

1     |  fr5| 1.4

To achive the same result in USQL i have tried the below and errors.

REFERENCE ASSEMBLY Staging.[Newtonsoft.Json];
REFERENCE ASSEMBLY Staging.[Microsoft.Analytics.Samples.Formats]; 
USING Microsoft.Analytics.Samples.Formats.Json;


DECLARE @inputmasterfileset string = "/sourcedata/samplefreeresponse.txt";
DECLARE @outputfreeresponse string = "/sourcedata/samplefreeresponseoutput.txt";


@freeresponse1 =
EXTRACT rowid string,
        freeresponses  string
FROM @inputmasterfileset
USING new JsonExtractor("");


@freeresponse2 =
SELECT rowid,
       JsonFunctions.JsonTuple(freeresponses).Values AS freeresponses
FROM @freeresponse1;

@freeresponse3 =
SELECT rowid
       ,JsonFunctions.JsonTuple(free) AS values
FROM @freeresponse2
     CROSS APPLY
         EXPLODE(freeresponses) AS c(free);

OUTPUT @freeresponse3
TO @outputfreeresponse
USING Outputters.Text('|', outputHeader:true,quoting:false);

The catch is, i dont know how the keys are named the json document so i cannot specify JsonFunctions.JsonTuple(free)["fr1"] in stage 3 of the code and I want the result as same as what i got in T-SQL.

Much appreciated.

Answer 1

I have resolved it myself. It was the confusion with SQL MAP and SQL ARRAY.