如何在 JSON 数组中查找元素的索引(PostgreSQL 9.3)?

Question

我有一张表格叫做 radio responses，其中的数据需要进行聚合处理，如下所示：

SELECT question_id, arr FROM radio_responses;

  question_id |       arr       
-------------+-----------------
          73 | [1,0,0]
          73 | [1,0,0]
          73 | [0,1,0]
          73 | [0,1,0]
          73 | [0,1,0]
          73 | [0,0,1]
          73 | [0,1,0]
          73 | [0,1,0]
          73 | [0,0,1]
          73 | [0,0,1]
          73 | [1,0,0]
          74 | [1,0]
          74 | [0,1]
          74 | [1,0]
          74 | [0,1]
          74 | [1,0]
          74 | [0,1]
          77 | [0,1]
          77 | [0,1]
          77 | [0,1]

我的最终目标是从每个数组中提取1的索引。我没有找到任何可用于JSON类型的函数来完成此操作，但我发现如果我有一个int[]数组，则可以使用idx()来实现。

我尝试了各种解决方案，但它们似乎都依赖于先取消嵌套数据，这似乎是不必要的，特别是因为在此过程中会丢失信息（除非有什么我错过了使用WITH ORDINALITY的方法）。

我正在使用Postgres 9.3版本。

- Max Candocia

---

with t(x) as (values('[1,0,0]'::json)) select position('1' in translate(x::text,'[],','')) from t; - Abelisto

抱歉，但我认为您的数据库设计完全错误。您不应该在此处使用 JSON 或数组，而应规范化您的表格。您甚至有重复项。77 | [0,1] 77 | [0,1] 77 | [0,1] - e4c5

http://xyproblem.info/ - David דודו Markovitz

由于您没有使用整数数组，请更改示例以适应实际用例。 - David דודו Markovitz

它是以JSON格式呈现的。原始数组看起来像这样：[[2000],[1,0,0],[0,1],[12],[1,1,0]]，每一行都是如此。我使用交叉连接扩展了它，并使用->运算符提取与特定问题类型相对应的值。该操作产生的数据仍然是JSON格式。 - Max Candocia

Answer 1

select      *
          ,(select min(i) + 1
            from   generate_series(0,json_array_length(arr)-1) as gs (i)
            where  (arr->>i)::int = 1
            )                           as ind

from        radio_responses
;

---

+-------------+---------+-----+
| question_id | arr     | ind |
+-------------+---------+-----+
| 73          | [1,0,0] | 1   |
+-------------+---------+-----+
| 73          | [1,0,0] | 1   |
+-------------+---------+-----+
| 73          | [0,1,0] | 2   |
+-------------+---------+-----+
| 73          | [0,1,0] | 2   |
+-------------+---------+-----+
| 73          | [0,1,0] | 2   |
+-------------+---------+-----+
| 73          | [0,0,1] | 3   |
+-------------+---------+-----+
| 73          | [0,1,0] | 2   |
+-------------+---------+-----+
| 73          | [0,1,0] | 2   |
+-------------+---------+-----+
| 73          | [0,0,1] | 3   |
+-------------+---------+-----+
| 73          | [0,0,1] | 3   |
+-------------+---------+-----+
| 73          | [1,0,0] | 1   |
+-------------+---------+-----+
| 74          | [1,0]   | 1   |
+-------------+---------+-----+
| 74          | [0,1]   | 2   |
+-------------+---------+-----+
| 74          | [1,0]   | 1   |
+-------------+---------+-----+
| 74          | [0,1]   | 2   |
+-------------+---------+-----+
| 74          | [1,0]   | 1   |
+-------------+---------+-----+
| 74          | [0,1]   | 2   |
+-------------+---------+-----+
| 77          | [0,1]   | 2   |
+-------------+---------+-----+
| 77          | [0,1]   | 2   |
+-------------+---------+-----+
| 77          | [0,1]   | 2   |
+-------------+---------+-----+

- David דודו Markovitz

Answer 2

你的问题实际上与关系型数据库管理系统无关。但是，如果你不想使用拆分和其他方法，并且卡在9.3版本上：

create or replace function json_array_position(a json, e int) returns int language plpythonu stable as $$
  import json;
  r = json.loads(a)
  return r.index(e)
$$;

select json_array_position('[1,2,3]'::json, 2);

- Abelisto