如何在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。

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   |
+-------------+---------+-----+

Answer 2

您的问题实际上与rdbms无关。但是，如果您不想使用不打击等等，并且是否粘在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);