Postgres SQL查询特定元素中的数组text [] [英] Postgres SQL to query array text[] in specific element

问题描述

这是我要查询的数据：
表名： test，列 data

This is the data that I'm trying to query: Table name: "test", column "data"

7;"{{Hello,50},{Wazaa,90}}"
8;"{{Hello,50},{"Dobar Den",15}}"

要查询此数据，我正在使用以下SQL查询：

To query this data I'm using this SQL query:

SELECT *, pg_column_size(data) FROM test WHERE data[1][1] = 'Hello'

例如，如何在所有元素中但在第一个子元素中而不在第二个元素中进行搜索：

How I can search in all elements but in the first sub element and not in the second for example:

SELECT *, pg_column_size(data) FROM test WHERE data[][1] = 'Hello'

因为如果我这样搜索：

SELECT *, pg_column_size(data) FROM test WHERE data[1][1] = "Wazaa"

它不会返回任何内容，因为我正在努力查看子元素，我必须像这样修改它：

it won't return anything because I'm hardcoding to look at first sub element and I have to modify it like this:

SELECT *, pg_column_size(data) FROM test WHERE data[2][1] = 'Wazaa'

如何检查所有父元素和第一个子元素？

How to make it to check all parent elements and first sub element?

有使用 ANY查询所有元素的解决方案但是我不想触摸where语句中的第二个元素，因为如果我在第一个子元素中有数字，它将查询第二个参数，它也是数字。

there is solution using "ANY" to query all elements but I don't want to touch second element in where statement because if I have numbers in first sub element it will query the second parameter which is also number.

SELECT * FROM test WHERE '90' = ANY (data);

推荐答案

PostgreSQL对数组的支持不是特别好。您可以很容易地 unnest 一维数组，但是n维数组可以完全展平，而不仅仅是第一维。不过，您仍然可以使用这种方法来查找所需的记录集，但这很丑陋：

PostgreSQL's support for arrays is not particularly good. You can unnest a 1-dimensional array easy enough, but a n-dimensional array is completely flattened, rather than only the first dimension. Still, you can use this approach to find the desired set of records, but it is rather ugly:

SELECT test.*, pg_column_size(test.data) AS column_size
FROM test
JOIN (SELECT id, unnest(data) AS strings FROM test) AS id_strings USING (id)
WHERE id_strings.strings = 'Wazaa';

或者，写此函数将二维数组简化为一维数组的记录，然后您基本上可以在问题中使用所有SQL查询。

Alternatively, write this function to reduce a 2-dimensional array into records of 1-dimensional arrays and then you can basically use all of the SQL queries in your question.

这篇关于Postgres SQL查询特定元素中的数组text []的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！