alamb commented on issue #14768:
URL: https://github.com/apache/datafusion/issues/14768#issuecomment-2676887133
```sql
> create or replace table my_table as values ({'a': 'A1', 'myobjects':
[{'name': '1', 'value': 'V'}, {'name': '2', 'value': 'V2'}]});
0 row(s) fetched.
Elapsed 0.004 seconds.
> select * from my_table;
+-----------------------------------------------------------------+
| column1 |
+-----------------------------------------------------------------+
| {a: A1, myobjects: [{name: 1, value: V}, {name: 2, value: V2}]} |
+-----------------------------------------------------------------+
1 row(s) fetched.
Elapsed 0.001 seconds.
```
You can select the inner object like
```sql
> SELECT column1['myobjects'] from my_table;
+---------------------------------------------+
| my_table.column1[myobjects] |
+---------------------------------------------+
| [{name: 1, value: V}, {name: 2, value: V2}] |
+---------------------------------------------+
> SELECT column1['myobjects'][1] from my_table;
+---------------------------------------------+
| my_table.column1[myobjects][Int64(1)][name] |
+---------------------------------------------+
| 1 |
+---------------------------------------------+
1 row(s) fetched.
Elapsed 0.001 seconds.
```
You can use some combination of `unnest` to get just the name values:
Docs are here:
https://datafusion.apache.org/user-guide/sql/special_functions.html#unnest
```sql
> SELECT unnest(column1['myobjects']) from my_table;
+-------------------------------------+
| UNNEST(my_table.column1[myobjects]) |
+-------------------------------------+
| {name: 1, value: V} |
| {name: 2, value: V2} |
+-------------------------------------+
2 row(s) fetched.
Elapsed 0.003 seconds.
> SELECT unnest(column1['myobjects'])['name'] from my_table;
+-------------------------------------------+
| UNNEST(my_table.column1[myobjects])[name] |
+-------------------------------------------+
| 1 |
| 2 |
+-------------------------------------------+
2 row(s) fetched.
Elapsed 0.003 seconds.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org
For additional commands, e-mail: github-h...@datafusion.apache.org
