You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by "mbeckerle (via GitHub)" <gi...@apache.org> on 2023/10/08 12:31:17 UTC
[I] json example on drill site doesn't work (drill)
mbeckerle opened a new issue, #2834:
URL: https://github.com/apache/drill/issues/2834
Using drill 1.21
The example on page https://drill.apache.org/docs/json-data-model/ of querying the citylots.json does not work:
```
SELECT features[0].properties.MAPBLKLOT, FROM dfs.`/home/mbeckerle/Downloads/citylots.json.gz`;
Error: PARSE ERROR: Encountered "." at line 1, column 19.
SQL Query: SELECT features[0].properties.MAPBLKLOT, FROM dfs.`/home/mbeckerle/Downloads/citylots.json.gz`
^
[Error Id: bfb6226d-273f-4a9f-a58b-81c9cd32c18c ] (state=,code=0)
```
In addition, I've tried numerous variants of this query to see if I can find out what is wrong, but I can't get anything to work.
This much works:
```
SELECT flatten(features) as feature FROM dfs.`/tmp/citydata.json`;
+----------------------------------------------------------------------------------+
| feature |
+----------------------------------------------------------------------------------+
| {"type":"Feature","properties":{"MAPBLKLOT":"0001001","BLKLOT":"0001001","BLOCK_NUM":"0001","LOT_NUM":"001","FROM_ST":"0","TO_ST":"0","STREET":"UNKNOWN","ODD_EVEN":"E"},"geometry":{"type":"Polygon","coordinates":[[[-122.42200352825247,37.80848009696725,0.0],[-122.42207601332528,37.808835019815085,0.0],[-122.42110217434863,37.808803534992904,0.0],[-122.42106256906727,37.80860105681815,0.0],[-122.42200352825247,37.80848009696725,0.0]]]}} |
| {"type":"Feature","properties":{"MAPBLKLOT":"0002001","BLKLOT":"0002001","BLOCK_NUM":"0002","LOT_NUM":"001","FROM_ST":"0","TO_ST":"0","STREET":"UNKNOWN","ODD_EVEN":"E"},"geometry":{"type":"Polygon","coordinates":[[[-122.42082593937107,37.80863147414603,0.0],[-122.4208580496797,37.80879564136959,0.0],[-122.4198119587043,37.80876180971401,0.0],[-122.42082593937107,37.80863147414603,0.0]]]}} |
| {"type":"Feature","properties":{"MAPBLKLOT":"0004002","BLKLOT":"0004002","BLOCK_NUM":"0004","LOT_NUM":"002","FROM_ST":"0","TO_ST":"0","STREET":"UNKNOWN","ODD_EVEN":"E"},"geometry":{"type":"Polygon","coordinates":[[[-122.41570120460688,37.80832725267146,0.0],[-122.4157607435932,37.808630700240904,0.0],[-122.4137878913324,37.80856680131984,0.0],[-122.41570120460688,37.80832725267146,0.0]]]}} |
+----------------------------------------------------------------------------------+
```
But if I then try this in a sub-select to try to dig out the MAPBLKLOT....
```
select t.properties.MAPBLKLOT from (SELECT flatten(features) as feature FROM dfs.`/tmp/citydata.json`) t;
select t.properties.MAPBLKLOT from (SELECT flatten(features) as feature FROM dfs.`/tmp/citydata.json`) t;
Error: PARSE ERROR: Encountered "." at line 1, column 9.
SQL Query: select t.properties.MAPBLKLOT from (SELECT flatten(features) as feature FROM dfs.`/tmp/citydata.json`) t
^
[Error Id: 49670771-e7d5-41c7-9ede-9fa42e3ff261 ] (state=,code=0)
```
--
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: dev-unsubscribe@drill.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] json example on drill site doesn't work (drill)
Posted by "mbeckerle (via GitHub)" <gi...@apache.org>.
mbeckerle commented on issue #2834:
URL: https://github.com/apache/drill/issues/2834#issuecomment-1757564537
Fixed in drill-site commit de30f12f4b37f73caac35660d0cae8f8d88f9b7a
--
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: dev-unsubscribe@drill.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] json example on drill site doesn't work (drill)
Posted by "mbeckerle (via GitHub)" <gi...@apache.org>.
mbeckerle closed issue #2834: json example on drill site doesn't work
URL: https://github.com/apache/drill/issues/2834
--
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: dev-unsubscribe@drill.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] json example on drill site doesn't work (drill)
Posted by "mbeckerle (via GitHub)" <gi...@apache.org>.
mbeckerle commented on issue #2834:
URL: https://github.com/apache/drill/issues/2834#issuecomment-1752028910
It appears 'properties' is a keyword. This works, note the backticks around `properties`
```
select t.feature.`properties` as property from (SELECT flatten(features) as feature FROM dfs.`/tmp/citydata.json`) t;
select t.feature.`properties` as property from (SELECT flatten(features) as feature FROM dfs.`/tmp/citydata.json`) t;
+----------------------------------------------------------------------------------+
| property |
+----------------------------------------------------------------------------------+
| {"MAPBLKLOT":"0001001","BLKLOT":"0001001","BLOCK_NUM":"0001","LOT_NUM":"001","FROM_ST":"0","TO_ST":"0","STREET":"UNKNOWN","ODD_EVEN":"E"} |
| {"MAPBLKLOT":"0002001","BLKLOT":"0002001","BLOCK_NUM":"0002","LOT_NUM":"001","FROM_ST":"0","TO_ST":"0","STREET":"UNKNOWN","ODD_EVEN":"E"} |
| {"MAPBLKLOT":"0004002","BLKLOT":"0004002","BLOCK_NUM":"0004","LOT_NUM":"002","FROM_ST":"0","TO_ST":"0","STREET":"UNKNOWN","ODD_EVEN":"E"} |
+----------------------------------------------------------------------------------+
3 rows selected (0.157 seconds)
apache drill (dfs.tmp)>
```
--
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: dev-unsubscribe@drill.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] json example on drill site doesn't work (drill)
Posted by "mbeckerle (via GitHub)" <gi...@apache.org>.
mbeckerle commented on issue #2834:
URL: https://github.com/apache/drill/issues/2834#issuecomment-1752019294
I found this works, but I don't know why the "['properties']" notation is needed vs. just "."
```
select t.feature['properties']['MAPBLKLOT'] as MAPBLKLOT from (SELECT flatten(features) as feature FROM dfs.`/tmp/citydata.json`) t;
select t.feature['properties']['MAPBLKLOT'] as MAPBLKLOT from (SELECT flatten(features) as feature FROM dfs.`/tmp/citydata.json`) t;
+-----------+
| MAPBLKLOT |
+-----------+
| 0001001 |
| 0002001 |
| 0004002 |
+-----------+
3 rows selected (0.128 seconds)
apache drill>
```
--
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: dev-unsubscribe@drill.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org