CODEWITHSUNDEEP
sqlarraysjsonpostgresqlpostgresql-9.3
Max Candocia
Max Candocia

Reputation: 4385

How to find the index of an element within JSON array (PostgreSQL 9.3)?

I have some data in a table radio responses I am aggregating that looks like this:

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]

My end goal is to extract the index of the 1 from each array. I could not find any functions to do so with the JSON type, but I did find that I could do so with idx() if I have an int[] array.

I have tried various solutions, but they all seem to rely on unnesting the data first, which seems unnecessary, especially since information is lost in the process (unless there's something that uses WITH ORDINALITY that I missed).

I am using Postgres version 9.3.

Upvotes: 3

Views: 2960

Answers (2)

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

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

Upvotes: 2

Abelisto
Abelisto

Reputation: 15614

Your question actually is not about RDBMS. However if you don't want to use unnesting and so on and if you stuck on the 9.3 version:

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);

Upvotes: 1

Related Questions