SQL old driver, calculate array & map & JSON data in SQL

Source: Internet
Author: User

Summary: Scenarios Typically, we work with data, a column of data types either as strings or as numbers, which are primitive types of data.

Scene

In general, we work with data, a column of data types that are either strings or numbers, which are primitive types of data. In some complex business scenarios, we use complex formats in a column, such as array arrays, objects (map), JSON, and other formats to represent complex data, such as:

array_columnis an array type. If we want to count the aggregated values of all the values in the Array_column, then we have to iterate through each element in the array of each row.

Unnest syntax

  • Unnest (Array) as Table_alias (column_name)
    Indicates that the array type is expanded to multiple rows, and the name of the row is column_name.

  • Unnest (map) as table (Key_name, Value_name)

    表示把map类型展开成多行,key的名称为key_name, value的名称为value_name

  • Note that since Unnest receives data from an array or map type, if the user's input is a string type, it must first be converted to a JSON type and then converted to an array type or map type, in the waycast(json_parse(array_column) as array(bigint))

Iterate through each element of an array

Use SQL to expand the array into multiple lines:

* | select  array_column, a   from log, unnest( cast( json_parse(array_column)   as array(bigint) ) ) as  t(a)

The above SQL expands the array into multiple lines of numbers, unnest( cast( json_parse(array_column) as array(bigint) ) ) as t(a) unnest syntax expands the array, names the newly generated table as T, and uses a to refer to the expanded column. Results such as:

    • The and of each element in the statistics array

* | select   sum(a)    from log, unnest( cast( json_parse(array_column)   as array(bigint) ) ) as  t(a)

    • Group by calculation by each element in the array

* | select   a, count(1)    from log, unnest( cast( json_parse(array_column)   as array(bigint) ) ) as  t(a)     group by a

Traverse Map

    • Traversing elements in a map

* | select  map_column , a,b    from log, unnest( cast( json_parse(map_column)   as map(varchar, bigint) ) ) as  t(a,b)

    • Group by statistics according to the map key

* | select   key,  sum(value)    from log, unnest( cast( json_parse(map_column)   as map(varchar, bigint) ) ) as  t(key,value)    GROUP  BY  key

Formatting displays the results of the Histogram,numeric_histogram

1.histogram

The histogram function is similar to the Count group by syntax. Syntax reference documentation.

Usually we see the results of histogram as follows:

* | select histogram(method)

is a bunch of JSON, unable to configure the view display, we can use the Unnest syntax to expand the JSON into a multi-line configuration view, for example:

* | select  key , value  from( select histogram(method) as his from log) , unnest(his ) as t(key,value)

Next, you can configure the visual view:

2. Numeric_histogram

The Numeric_histogram syntax is to assign a numeric column to a number of buckets, which is equivalent to a group by of a numeric column, a specific syntax reference document

* | select numeric_histogram(10,Latency)

The output of the Numeric_histogram is as follows:

To format this result, we write sql:

* |  select key,value from(select numeric_histogram(10,Latency) as his from log) , unnest(his) as t(key,value)

The results are as follows:

At the same time configure the form of histogram display:

Original link


SQL old driver, calculate array & map & JSON data in SQL

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.