Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to use sql to query nested data types". In the operation of actual cases, many people will encounter this dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

[detailed description of the problem]

See the data in the figure select.png. The PageSize in the figure belongs to the data inside the array. Now you only need to query the PageSize. That is, queries that use sql to query nested data.

[review opinion]

1. For the problem list that has been solved, the "solution result" is marked as "solved"

2. Don't simply describe the problem in the "problem description" as shown in the picture. Try to describe the problem in the picture.

3. In the "solution", don't simply describe it as shown in the picture. The sentences in the picture can be described in words.

Introduce it as an example; links can be posted for knowledge points related to official documents

4. Individuals need to know why the query command is written in this way, so as to facilitate flexible application in the future.

[solution]

1. Direct query

Based on the data in figure select.png, you can derive the query statement: db.exec ("select T.Details.$ [0] .PageSize from $SNAPSHOT_CL as T where T.Name =" my.my "group by T.Name").

The query results are shown in figure solve1.png.

What needs to be noticed here is the use of the as alias in the statement and the operation of reading the data in the array.

Scope of use of as aliases: if the query source is not a collection, all fields in the query at this level need to refer to aliases (except *), for example: select T.a, T.b from (select * from foo.bar) as T where T.a < 10.

For more information, please see:

(1) the usage of select: http://doc.sequoiadb.com/cn/SequoiaDB-cat_id-1432190960-edition_id-0.

(2) the usage of as: http://doc.sequoiadb.com/cn/index-cat_id-1432190968-edition_id-300.

How to read the data in the array: use "array name. $[index]", and index is the subscript. For example, T.Details.$ [0] is the first element that represents the Details array.

For more information, please see:

(1) array: http://doc.sequoiadb.com/cn/index-cat_id-1519612291-edition_id-300.

(2) the use of $+ identifier: http://doc.sequoiadb.com/cn/index-cat_id-1432190918-edition_id-208.

2. Query after splitting the array with split by

Query statement: db.exec ("select K.Details.PageSize as PageSize from (select * from $SNAPSHOT_CL as T where T.Name =" my.my "split by T.Details) as K").

The query results are shown in figure solve2.png.

Use split by to split the array first, and then directly use the array name. the name of the field to query to query the relevant information.

That's all for "how to use sql to query nested data types". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.