Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Query to extract Key Value pair from Json #148

Closed
anbudhanush opened this issue Dec 17, 2018 · 1 comment
Closed

Query to extract Key Value pair from Json #148

anbudhanush opened this issue Dec 17, 2018 · 1 comment

Comments

@anbudhanush
Copy link

anbudhanush commented Dec 17, 2018

Hi, I am trying to extract Key and Values along with row id for each key and value. Here is the json example.
{"rowid": "1", "freeresponses": { "fr1": "1.1", "fr2": "1.1", "fr3": "1.3", "fr4": "1.4", "fr5": "1.4", "fr6": "1.4" }}
I tried this with t-SQL and i get desired result.
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;
TSQL results
1 fr1 1.1
1 fr2 1.1
1 fr3 1.3
1 fr4 1.4
1 fr5 1.4

I tired the following USQL

`//Read Json
@freeresponse1 =
EXTRACT rowid string,
freeresponses string
FROM @inputfile
USING new MultiLevelJsonExtractor("*", false,
"rowid",
"freeresponses");

// Convert the JSON column to SQL MAP to multiple rows
@freeresponsevalues =
SELECT rowid,
JsonFunctions.JsonTuple(freeresponses).Values AS frvalues
FROM @freeresponse1;
//Explode Tuple
@freeresponsevaluesx =
SELECT rowid
,c.value AS frvalues
FROM @freeresponsevalues
CROSS APPLY
EXPLODE(frvalues) AS c(value);`

I am not able to get the keys as I know where that should be mentioned in USQL. I tired c(key,value) on the last line but that errors out.

Any help will be appreciated.

@anbudhanush
Copy link
Author

I have resolved the issue, It was SQL MAP Line. Closing the ticket.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

1 participant