How to use PostgreSQL json_array_elements whit WHERE <value> IN clause

Question

I have occasion and relation tables. relation.occasions is a json type field, which contains an array of occasion keys. I want to get occasion records by relation, where occasion.key in relation.occasions. Assume, that this is my DB data:

occasion

        key      |     name      |
-------------------------------------
    BIRTHDAY     |   Birthday    |
    ANNIVERSARY  |   Anniversary |

relation

    key          | occasions                         |
------------------------------------------------------
    FATHER       |   [ "BIRTHDAY", "ANNIVERSARY" ]   |
    FRIEND       |   [ "BIRTHDAY" ]                  |

Here is the query I'm trying to use:

SELECT * FROM occasion o WHERE o.key IN
(SELECT json_array_elements(r.occasions)::text from relation r WHERE r.key = 'FATHER')

The result is 0 rows instead of expected 2. Can somebody give me a hint what am I doing wrong? How else can I achieve desired result?

Patrick · Accepted Answer · 2016-07-02 12:12:47Z

5

The function json_array_elements() returns a set, not an array, so you should use it as a row source, not in the select list.

The correct query would be:

SELECT o.*
FROM occasion o
JOIN (
    SELECT DISTINCT j.value
    FROM relation
    JOIN LATERAL json_array_elements_text(occasions) j(value) ON true
    WHERE key = 'FATHER') sub ON o.key = sub.value;

You can also flatten it out without the sub-query, but I find this version to be more readable (the query planner will do the flattening anyway).

answered Jul 2, 2016 at 12:12

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Abelisto Over a year ago

select o.* from occasion o join relation r on (o.key = any(select json_array_elements_text(r.occasions)) and r.key = 'FATHER');

- simpler a bit, but assuming that te keys is unique.

Patrick Over a year ago

@Abelisto Using a SRF in a select list should be avoided: They are used like a table, view, or subquery in the FROM clause of a query.

Abelisto Over a year ago

Your link is exactly about FROM clause, not about SRF usage. Set Returning Functions, example from the official documentation: SELECT generate_series(1.1, 4, 1.3);

Patrick Over a year ago

@Abelisto The documentation is certainly far from perfect on this issue, but that doesn't make your suggestion right. Any function that returns a set of rows is by definition a SRF (or table function, or row source) and should thus be used in the FROM clause. The simple reason for that is consistency and predictability. For instance, with a table a with 5 rows, how many rows do you get with SELECT a.*, generate_series(1, 5) FROM a?

Abelisto Over a year ago

25 obviously. I really can not understand why do you think that it is not "consistent and predictable". BTW, C++ also hard to understand for many, but it is not the reason to say "using C++ should be avoided".

|

Collectives™ on Stack Overflow

How to use PostgreSQL json_array_elements whit WHERE <value> IN clause

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related