[
https://issues.apache.org/jira/browse/PHOENIX-1710?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14498922#comment-14498922
]
Aakash Pradeep commented on PHOENIX-1710:
-----------------------------------------
Postgres has these operators to read JsonArray
#>> , ->> and -> . Most of them just take integer as an argument to specify
array index.
json_data #>> '{a,2}' where 'a' is the first level key and 2 specify the array
index to be read.
josn_data->> 2 to read second item from json array
json->3 to read second item from json array
But there is no function in postgres which allow you to read a single array
value. http://www.postgresql.org/docs/9.4/static/functions-json.html
So we have 3 options :
1. Allow something like json_extract_path_text(json_data, ARRAY['a','2','b']).
Here we will have to check Json object represented by each path if it is an
Json array then read the next path as an integer otherwise treat them as key.
This should be same as Postgres operator "#>>".
2. Use '{}' to specify array index like json_extract_path_text(json_data,
ARRAY['a','{2}','b'])
3. Use '[]' to specify array index like json_extract_path_text(json_data,
ARRAY['a','[2]','b'])
I will prefer Option 1 and Option 3. Options 3 seems better to me, since that
follows the so called specification for JsonPath
http://goessner.net/articles/JsonPath/.
This JsonPath(https://github.com/jayway/JsonPath) library is also based on the
same specification which is used by Apache Tajo to implement there
json_extraxt_path_text (
https://issues.apache.org/jira/browse/TAJO-1529?jql=text%20~%20json_extract_path
).
Please let me know your opinion.
> Implement the json_extract_path_text built-in function
> ------------------------------------------------------
>
> Key: PHOENIX-1710
> URL: https://issues.apache.org/jira/browse/PHOENIX-1710
> Project: Phoenix
> Issue Type: Sub-task
> Reporter: James Taylor
> Assignee: Aakash Pradeep
>
> Implement the json_extract_path_text modeled after the Postgres function.
> This function returns JSON pointed to by the path elements argument. In
> Phoenix, it could be implemented like this:
> {code}
> VARCHAR json_extract_path_text (VARCHAR json, VARCHAR ARRAY path_elems paths)
> {code}
> For example:
> {code}
> SELECT json_extract_path_text(json_col, ARRAY['f4','f6']) FROM my_table;
> {code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
