How to handle potentially empty json in postgres CTE (with json_each function)

SQL newbie here. I'm using Grafana and Postgresql to build a visualization PoC, so it's pretty crude, but I want to learn and figure things out by myself. I'm using a customized filters variable, which could be empty string.

I have a CTE set up like this:

with filter1 as (
    select json_array_elements_text(value) from json_each('${filters}') where key = 'filter1'
)

select case when '{$filters}' != '{}'
    then sum(case when field in (select * from filter1) then 1 else 0 end)
    -- then ... want to add multiple filters
    else count(*)
  end
from data;

So the filters could be empty, and that's when the error happens. I was hoping that the CTE would only evaluate when it's invoked in the case statement, but apparently that's not the case.

I have commented out different parts of the query, and I know that the error is on the json_each function when the filters variable is empty. The error is pq: invalid input syntax for type json.

I have tested, so when the filters is empty string, the value is interpreted as '{}'.

I somehow tried json_each(coalesce('${filters}', '{}'::json)), but it seems like json_each is expecting non-empty json.

> I somehow tried json_each(coalesce('${filters}', '{}'::json)), but it seems like json_each is expecting non-empty json.

There are two problems here apart from the CASE/CTE thing. Coalesce expects all of its input to be the same type, so telling it the 2nd one is json will force the 1st one to also be json, which just invokes the error you are trying to avoid. Do the casting after the coalesce, not inside of it:

coalesce('${filters}', '{}')::json

But that still won't work if ${filters} is the empty string, because coalesce doesn't consider the empty string to be the same as NULL. You can fix this with NULLIF.

coalesce(nullif('${filters}',''), '{}')::json

But once you have the NULLIF, you no longer need the coalesce, since NULL is a legal value for use with json_each, unlike the empty string.

Once you know the correct way to solve the problem, you can probably do it without the CTE.

I didn't test this exact construct, because '${filters}' is not a valid format to do parameters in PostgreSQL. That must be getting munged by the client-side library into the correct format, so I had to fake it in order to test it in psql.