Nested Queries from JSON

1

Hi, May I know how to query a field inside the nested json in dremio SQL please?
I have tried table.field.fieldname = xx, but not working
Thanks
Prem

2

Hi @prem

You need to do a series of unnest and extract. So before you do the table.field.fieldname, you need to Unnest (command is flatten).

You can do it either via the UI like the attached screenshot, use Unnest

unnest

(OR)

Via SQL like below

SELECT flatten(fragmentProfile) AS fragmentProfile
FROM “@dremio”.profile_attempt AS profile_attempt

Once it is flattened you can extract the particular field you are interested again in 2 ways

Either via the UI using the extract option. Click on the 3 dots to the right of any value and click extract. Screenshot below

extract
extract956×706 51.5 KB

(OR)

use the format you are trying “table.field.fieldname”, like below,

SELECT fragmentProfile, nested_0.fragmentProfile.minorFragmentProfile AS minorFragmentProfile
FROM (
SELECT flatten(fragmentProfile) AS fragmentProfile
FROM “@dremio”.profile_attempt AS profile_attempt
) nested_0

The only step you have missed in flatten (which is unnest)

Kindly let us know if you have any further questions

Thanks
@balaji.ramaswamy

3

Hi Balaji,

Thanks for that, the flatten, unnest, IN, Join queries were not supported in elastic, I have tried that, because of that reason I wish to see the query in the previous email how it is translated as elastic query.

Regards

Prem

4

Hi Balaji,
When I do that, all the fields with empty list, get deleted e.g.
I have a column called “data” with various nested json data (column 2), relating to different items in a States (column 1) :
States Data
CA [{“fields”:[{“value”:“0”,“key”:“roi”,“type”:“int64”},{“value”:“1”,“key”:“profit”,“type”:“int64”}],“timestamp”:1592413089590019}]
AZ [ ]
TX [{“fields”:[{“value”:“0”,“key”:“roi”,“type”:“int64”},{“value”:“1”,“key”:“profit”,“type”:“int64”}],“timestamp”:1592413089590019}]

When I unnest, I loose all the data in the AZ [ ] row . Is there a way to ensure the empty data lists don’t get deleted on unnesting?

Thanks

5

@drem

Is this a map data type?

Thanks
Bali

6

Hello,

Just curious, was the flatten function fixed? I see the same issue in Version 21, what’s the current workaround?

Thanks

7

HI Alina,

Flatten should work, is it the scenario here one of the lists is empty and hence it returns null, like below?

{"col1":1,"flatten1":[{"col2":"Stefan Edberg","col3":"Sweden"}],"flatten2":[{"col4":"Steffi Graf","col5":"Germany"}],"col6":"ATP_WTA"}
{"col1":2,"flatten1":[{"col2":"Gabriela Sabatini","col3":"Argentina"}],"flatten2":[],"col6":"ATP_WTA"}
8

Hi,

This thread has helped me.

Thank you very much

Thanks
Saidulu