get a single element from a JSON array in postgreSQL

Question

I need to retrieve a single element from an array stored in a postgreSQL DB according to some rules I define, and then join it with some other data. let me make an example:

postgres=# SELECT * FROM list;
id    | list
------+-----------------------------------------------------------------------------
0ab2c | [{type: 1, val: a}, {type: 3, val: c}, {type: 4, val: d}]
01abc | [{type: 1, val: a}, {type: 2, val: b}]
a0b31 | [{type: 1, val: a}, {type: 2, val: b}, {type: 3, val: c}]

postgres=# SELECT * FROM elems
eid | listId
----+-------
10  | 0ab2c
11  | 01abc
12  | a0b31

I would like to extract only an element from list table and join the result with elem table in order to obtain something like:

eid | listId | type | val
----+--------+------+-----
10  | 0ab2c  |   3  |  c
11  | 01abc  |   2  |  b
12  | a0b31  |   2  |  b

In particular the elements in the json array should be selected according to a function like (I wrote it in JS because I'm more familiar with it, but the function needs to be in plpgSQL of course):

function getElemFromList(list){
    var allowedTypes = [2, 3];
    var result;
    for(var i = 0; i < list.length; i++){
        if(list[i].indexOf(allowedTypes) !== -1){
            return list[i];
        }
    }
}

where the element in the list is taken by belonging to a certain type and by being the first allowed element in the array.

Answer 1

This should be simple enough. It looks like you need the operators from this page:

http://www.postgresql.org/docs/current/static/functions-json.html

And should you wish to loop over any result set in pl/pgsql then look into something like this:

DO $$
cur RECORD;
BEGIN
FOR cur IN SELECT * FROM LIST
//do something with your JSON
END LOOP;
END $$;