# Nested Queries from JSON

**URL:** <https://community.dremio.com/t/nested-queries-from-json/2772>\
**Category:** Uncategorized\
**Created:** [February 14, 2019, 1:19am UTC](https://community.dremio.com/t/nested-queries-from-json/2772 "2019-02-14T01:19:23Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![prem](https://avatars.discourse-cdn.com/v4/letter/p/8797f3/32.png) [@prem](https://community.dremio.com/u/prem)\
**Post date:** [February 14, 2019, 1:19am UTC](https://community.dremio.com/t/nested-queries-from-json/2772/1 "2019-02-14T01:19:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [February 14, 2019, 7:18am UTC](https://community.dremio.com/t/nested-queries-from-json/2772/2 "2019-02-14T07:18:31Z")

</div>

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](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/b/b1480dbc85a4e9f7a448246ca1b7a416f953db69.png)

(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](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/5/55c23bcff562d0a31902b4ae9c535a5f1f9d3850.png)

(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

---

<div class="post-metadata">

**Author:** ![prem](https://avatars.discourse-cdn.com/v4/letter/p/8797f3/32.png) [@prem](https://community.dremio.com/u/prem)\
**Post date:** [February 14, 2019, 11:25pm UTC](https://community.dremio.com/t/nested-queries-from-json/2772/3 "2019-02-14T23:25:28Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![drem](https://avatars.discourse-cdn.com/v4/letter/d/22d042/32.png) [@drem](https://community.dremio.com/u/drem)\
**Post date:** [June 18, 2020, 5:56pm UTC](https://community.dremio.com/t/nested-queries-from-json/2772/4 "2020-06-18T17:56:02Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [June 22, 2020, 6:00am UTC](https://community.dremio.com/t/nested-queries-from-json/2772/5 "2020-06-22T06:00:38Z")

</div>

@drem

Is this a map data type?

Thanks  
Bali

---

<div class="post-metadata">

**Author:** ![acornea](https://avatars.discourse-cdn.com/v4/letter/a/edb3f5/32.png) [@acornea](https://community.dremio.com/u/acornea)\
**Post date:** [March 16, 2023, 3:11am UTC](https://community.dremio.com/t/nested-queries-from-json/2772/6 "2023-03-16T03:11:35Z")

</div>

Hello,

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

Thanks

---

<div class="post-metadata">

**Author:** ![balaji.ramaswamy](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/balaji.ramaswamy/32/843_2.png) [@balaji.ramaswamy](https://community.dremio.com/u/balaji.ramaswamy)\
**Post date:** [April 9, 2023, 5:27am UTC](https://community.dremio.com/t/nested-queries-from-json/2772/7 "2023-04-09T05:27:32Z")

</div>

HI Alina,

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

```auto
{"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"}

```

---

<div class="post-metadata">

**Author:** ![SAIDULU](https://avatars.discourse-cdn.com/v4/letter/s/71c47a/32.png) [@SAIDULU](https://community.dremio.com/u/SAIDULU)\
**Post date:** [August 28, 2023, 2:02pm UTC](https://community.dremio.com/t/nested-queries-from-json/2772/8 "2023-08-28T14:02:33Z")

</div>

Hi,

This thread has helped me.

Thank you very much

Thanks  
Saidulu