[jira] [Commented] (IMPALA-376) Built-in functions for parsing JSON

["Greg Rahn (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/IMPALA-376?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16604677#comment-16604677 ] 

Greg Rahn commented on IMPALA-376:
----------------------------------

Sorry for being late to the party, but I'd strongly suggest that we follow the ANSI SQL implementation for json_value() as this seems to be very close to what is being implemented here but follows the ANSI SQL standard and thus will be much more portable and commonly used.

{{json_value(<json>, <path> [returning <type>])}}

Extracts a scalar JSON value—everything except object and array—and returns it as a native SQL type. The optional {{returning}} clause performs a typecast. Without a {{returning}} clause, {{json_value }}returns a string.

References
 * [https://modern-sql.com/blog/2017-06/whats-new-in-sql-2016#json_value]
 * [https://docs.oracle.com/database/121/SQLRF/functions093.htm#SQLRF56668]
 * [https://ocelot.ca/blog/blog/2015/11/26/standard-sqljson-and-mysql-5-7-json/]
 * [https://www.iso.org/standard/67367.html]
 * [http://standards.iso.org/ittf/PubliclyAvailableStandards/c067367_ISO_IEC_TR_19075-6_2017.zip]

 

 

 

> Built-in functions for parsing JSON
> -----------------------------------
>
>                 Key: IMPALA-376
>                 URL: https://issues.apache.org/jira/browse/IMPALA-376
>             Project: IMPALA
>          Issue Type: New Feature
>          Components: Backend
>    Affects Versions: Product Backlog
>         Environment: All supported environments
>            Reporter: Zoltan Toth-Czifra
>            Assignee: Quanlong Huang
>            Priority: Minor
>              Labels: built-in-function
>
> Hi,
> Hive comes with some useful built-in UDFs to process JSON objects.
> https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
> Namely:
> - get_json_object
> - json_tuple
> To make Impala and Hive tables and quieries more interchangable, I am proposing porting these UDFs to be part Impala's built in functions:
> http://www.cloudera.com/content/cloudera-content/cloudera-docs/Impala/latest/Installing-and-Using-Impala/ciiu_functions.html
> h4. Example
> Consider the following table *raw_log*
> ||action||parameters||
> |search|{"keyword":"hotel"}|
> |visit|{"url":"http://example.com"}|
> ...and the following query:
> {code}
> SELECT get_json_object(event_params, "$.keyword") AS keyword FROM raw_log WHERE action='search';
> {code}
> The query should return the following results:
> ||keyword||
> |hotel|



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-all-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org