snuyanzin commented on code in PR #29834: URL: https://github.com/apache/flink/pull/29834#discussion_r4515249284
########## docs/content/docs/sql/built-in-functions/variant/_index.md: ########## @@ -0,0 +1,203 @@ +--- +title: "Variant" +weight: 16 +type: docs +bookCollapseSection: true +sqlFunctions: variant +--- +<!-- +Licensed to the Apache Software Foundation (ASF) under one +or more contributor license agreements. See the NOTICE file +distributed with this work for additional information +regarding copyright ownership. The ASF licenses this file +to you under the Apache License, Version 2.0 (the +"License"); you may not use this file except in compliance +with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + +Unless required by applicable law or agreed to in writing, +software distributed under the License is distributed on an +"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +KIND, either express or implied. See the License for the +specific language governing permissions and limitations +under the License. +--> + +# Variant Functions + +Variant functions parse JSON and XML text into [`VARIANT`]({{< ref "docs/sql/reference/data-types" >}}#variant) values and read values out of them, such as `PARSE_JSON`, `PARSE_XML` and `VARIANT_GET`. To turn a `VARIANT` back into JSON text, use [`JSON_STRING`]({{< ref "docs/sql/built-in-functions/json" >}}). + +{{< sql_functions "variant" >}} + +## XML to VARIANT + +The `PARSE_XML` function maps the root element of an XML document to a `VARIANT` object. For +example: + +```sql +-- {"book":{"#":{"price":[1,2],"title":0},"@pages":"320","price":["12.50","13.50"],"title":"Dune"}} +PARSE_XML('<book pages="320"><title>Dune</title><price>12.50</price><price>13.50</price></book>') +``` + +Attributes become fields with an `@` prefix, and a child element that occurs more than once becomes +an array. The `#` field records the order of the children, as described below. It can be ignored +when reading fields. + +Values are strings, unless the document declares a type with `xsi:type`, see below. Since a cast +from a `VARIANT` never parses a string, cast a value to `STRING` first and convert it with a regular +cast: + +```sql +CAST(v['book']['title'] AS STRING) -- 'Dune' +CAST(v['book']['price'][2] AS STRING) -- '13.50' +CAST(CAST(v['book']['@pages'] AS STRING) AS INT) -- 320 +``` + +Each element is mapped by the following rules: + +| XML input | Stored `VARIANT` value | +|--------------------------------------------------------------------|-------------------------------------------------------------| +| Element with `xsi:nil="true"` | `null`, or field `$` with `null` if it has other attributes | +| Element without attributes and child elements | its text, or `""` if it is empty | +| Any other element | object | +| Attribute `name` | field `@name`, a `STRING` | +| Child element `name` | field `name` | +| Text of an element with attributes or child elements | field `$` | +| Child element or text that occurs more than once | array, in document order | +| Element with differently named children, or with children and text | field `#` with the order of its content | + +A `VARIANT` object doesn't keep the order of its fields, so the `#` field records it. It maps each +key to the position of its item, or to an array of positions if the key occurs more than once. +Positions count the child elements and texts of the element, starting at 0. For example: + +```sql +-- {"book":{"#":{"$":[1,3],"price":[2,4],"title":0}, +-- "$":["This is a description","Text in between"], +-- "@pages":"320", +-- "price":["12.50",{"$":"13.50","@currency":"EUR"}], +-- "title":"Dune"}} +PARSE_XML(' + <book pages="320"> + <title>Dune</title> + This is a description + <price>12.50</price> + Text in between + <price currency="EUR">13.50</price> + </book>') +``` + +Here, `title` is the first item, the two texts are the second and the fourth, and the two `price` +elements are the third and the fifth. The second `price` has an attribute, so it is stored as an +object, while the first one is a string. An element whose content has only one key has no `#` field, +since the order of an array is the document order. + +Whether a child element is stored as a single value or as an array depends on how often it occurs, +so two documents that follow the same schema can map to different shapes. The default behavior +ensures a space-efficient representation. With `PARSE_XML(xml, TRUE)`, every child element, text, +and position in a `#` field is stored as an array, even if it occurs only once. The root element is never wrapped, since a document has exactly one. + +```sql +-- {"book":{"title":["Dune"]}} +PARSE_XML('<book><title>Dune</title></book>', TRUE) +``` + +An `xsi:type` attribute parses the text of an element as the matching kind, which replaces the text, +or the `$` field if the element has other attributes. The prefix of the type is ignored, so `xs:int`, +`xsd:int`, and `int` are the same type. If the element has child elements or no text, the type isn't +listed below, or the text isn't a valid value of the type, the text stays a `STRING` and `xsi:type` +is kept as an attribute. For example, `<a xsi:type="int">5</a>` maps to `{"a":5}`, and +`<a xsi:type="int">five</a>` maps to `{"a":{"$":"five","@xsi:type":"int"}}`. The text has to be in +the lexical form that XML Schema defines for the type, so `1e5` isn't a `decimal`, `1f` isn't a +`float`, and `12:30` isn't a `time`. Years after 9999 and the time `24:00:00` stay a `STRING` as well. + +| `xsi:type` | Stored `VARIANT` kind | +|------------------------------|----------------------------------------------| +| `string` | `STRING` | +| `boolean` | `BOOLEAN`, from `true`, `false`, `1`, or `0` | +| `byte` | `TINYINT` | +| `short` | `SMALLINT` | +| `int` | `INT` | +| `long` | `BIGINT` | +| `integer`, `decimal` | `DECIMAL`, with at most 38 digits | +| `float` | `FLOAT`, except for `NaN` and infinity | +| `double` | `DOUBLE`, except for `NaN` and infinity | +| `date` without an offset | `DATE` | +| `time` without an offset | `TIME` | +| `dateTime` with an offset | `TIMESTAMP_LTZ` | +| `dateTime` without an offset | `TIMESTAMP` | + +The offset of a `dateTime` is `Z` or a signed `hh:mm` of at most 14 hours. It is used to compute the +instant and isn't stored, since a `TIMESTAMP_LTZ` has no offset. For example, +`2026-09-30T12:30:00+02:00`, `2026-09-30T10:30:00Z`, and `2026-09-30T06:00:00-04:30` map to the same +`TIMESTAMP_LTZ`. A `date` or `time` with an offset stays a `STRING`, since it can't be converted to an +instant without a date or a time. + +`TIME` keeps microseconds, so `12:30:45.1234567` is stored as `12:30:45.123456`. `TIMESTAMP` and +`TIMESTAMP_LTZ` keep nanoseconds, or microseconds outside the nanosecond range from 1677 to 2262. Review Comment: also there might be a question: if we are talking about nanos and micros where exactly boundaries are? Midnight, somewhere else? What about timezone? Is it same for every timezone? -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
