Using JSON Function with PostgreSQL Subqueries

Question

This is a follow-up from a previous question. Understanding what is returned when JSON functions are involved in a PostgreSQL query is creating difficulty in implementing what looks to be an otherwise valid solution.

The query below runs to completion, and returns one column with three rows as expected.

SELECT x.y->'c' as c
from json_array_elements('[
                            {"c": "str1"}, 
                            {"c": "str2"},
                            {"c": "str3"}
                           ]') x(y)

However, if I try to run the above as a subquery, either as part of an EXISTS statement or as a JOIN, I get errors. Here is the JOIN version...

SELECT fldc
FROM table t
LEFT JOIN (SELECT x.y->'c' as c
           from json_array_elements('[
                            {"c": "str1"}, 
                            {"c": "str2"},
                            {"c": "str3"}
                           ]') x(y)) as z
ON z.c = t.fldc
LIMIT 100

With a single arrow in the SELECT statement of the subquery, as illustrated above, the error message is ERROR: operator does not exist: json = character varying Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

With a double arrow, i.e. SELECT x.y->>'c' as c, the error message is ERROR: Function 'JSON_ARRAY_ELEMENTS' is not supported in this context.

(PostgreSQL is being run from Dataiku's Data-Science Studio (DSS), so I believe these error messages are generated by DSS, and are not coming directly from PostgreSQL).

I thought that once an output from a SELECT statement was generated, it is just like the output of any other SELECT statement, i.e. if the subquery can run as it standalone query on its own, it should fit smoothly into a larger query. Can anyone help with both the practice and theory of what is happening here, and whether additional data-transformations or casting is needed and/or expected?

score 1 · Answer 1 · answered Jul 03 '21 at 19:01

1

you need to use ->> instead of -> to return a text value rather than a json value. you don't actuall yneed the subquery either.

SELECT fldc
FROM table t
LEFT JOIN json_array_elements('[
                            {"c": "str1"}, 
                            {"c": "str2"},
                            {"c": "str3"}
                           ]') x(y)
    ON t.fldc = x.y->>'c'
LIMIT 100

Online example

answered Jul 03 '21 at 19:01

Proposed solution results in error message ```ERROR: Function 'JSON_ARRAY_ELEMENTS' is not supported in this context```. – NaiveBayesian Jul 03 '21 at 19:18
Well, that is not a Postgres error message. – Jul 03 '21 at 19:28

Serg · Answer 2 · 2021-07-03T17:05:00.183

0

You can't compare json and text directly. Cast json to text first

SELECT fldc
FROM (
 select '"123"' fldc
 )t
LEFT JOIN (SELECT(x.y->'c')::varchar(100) as c
           from json_array_elements('[
                            {"c": "str1"}, 
                            {"c": "str2"},
                            {"c": "str3"}
                           ]') x(y)) as z
ON z.c = t.fldc

db<>fiddle

edited Jul 03 '21 at 17:05

answered Jul 03 '21 at 16:33

Serg

22,285
5
21
48

Unfortunately, the query above is resulting in the same behavior as before. The subquery works fine as a standalone query, but trying to use it as a subquery generates an error message of ```ERROR: Function 'JSON_ARRAY_ELEMENTS' is not supported in this context.``` – NaiveBayesian Jul 03 '21 at 16:58
Added the dbfiddle. – Serg Jul 03 '21 at 17:05
Everything is pointed in the direction of the implementation of PostgreSQL/JSON that I am using being limited in some way, but I don't understand what kind of limitation would cause a query that works standalone to be not usable as a subquery. – NaiveBayesian Jul 03 '21 at 17:18

Using JSON Function with PostgreSQL Subqueries

2 Answers2

Linked