[
https://issues.apache.org/jira/browse/CALCITE-7512?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18080232#comment-18080232
]
terran2010 commented on CALCITE-7512:
-------------------------------------
Thank you all for your suggestions. Next, I will refine the JIRA description
and pull request (PR)[~jensen] [~julianhyde]
> Support array operators for PostgreSql
> --------------------------------------
>
> Key: CALCITE-7512
> URL: https://issues.apache.org/jira/browse/CALCITE-7512
> Project: Calcite
> Issue Type: New Feature
> Components: babel
> Affects Versions: 1.41.0
> Reporter: terran2010
> Priority: Major
> Labels: pull-request-available
>
> Currently, babel does not support "@> <@ &&". Upon checking other database
> documentation, it is found that only PostgreSQL and databases compatible with
> PostgreSQL support the above operation symbols. Below are some examples of
> the usage of these operators
> {code:java}
> -- @> contains: left array contains all elements of right array
> SELECT ARRAY[1,2,3] @> ARRAY[2,3]; -- true, [1,2,3] contains [2,3]
> SELECT ARRAY[1,2,3] @> ARRAY[2,4]; -- false, 4 is not in left array-- <@
> is contained by: left array is subset of right array
> SELECT ARRAY[2] <@ ARRAY[1,2,3]; -- true, [2] is subset of [1,2,3]
> SELECT ARRAY[2,4] <@ ARRAY[1,2,3]; -- false, 4 is not in right array-- &&
> overlap: two arrays have common elements
> SELECT ARRAY[1,2] && ARRAY[2,3]; -- true, common element 2
> SELECT ARRAY[1,2] && ARRAY[3,4]; -- false, no common elements{code}
> {code:java}
> -- @> contains: left JSON object contains all key-value pairs of right
> SELECT '{"a":1,"b":2}'::jsonb @> '{"b":2}'::jsonb; -- true, {"b":2} is
> contained
> SELECT '{"a":1,"b":2}'::jsonb @> '{"b":3}'::jsonb; -- false, value of b
> differs
> SELECT '{"a":1,"b":2}'::jsonb @> '{"c":1}'::jsonb; -- false, key c does
> not exist-- <@ is contained by: left JSON is subset of right JSON
> SELECT '{"b":2}'::jsonb <@ '{"a":1,"b":2}'::jsonb; -- true, subset
> relationship-- && have common top-level keys: two JSON objects share keys
> SELECT '{"a":1,"b":2}'::jsonb && '{"b":3,"c":4}'::jsonb; -- true, common key
> "b"
> SELECT '{"a":1,"b":2}'::jsonb && '{"c":3,"d":4}'::jsonb; -- false, no common
> keys{code}
> This time, we aim to complete the parsing support for "@> <@ &&" and already
> have support for array function operations
> We can refer postgresql document about Position:
> [https://www.postgresql.org/docs/current/functions-array.html]
> We can consider supporting the above situation.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
