CODEWITHSUNDEEP
sqljsonpostgresql
Gh05d
Gh05d

Reputation: 8982

Use dynamically generated value to check whether a key exists in postgres json

I want to check for keys in my json datatype, where the key is the number of a week. Basically, I use this function to get the number of the next week: extract(week FROM current_date) + 1

Now I want to use the returned value, to check whether this key exists in my object and if not return an empty object:

SELECT dv.unitid as id,
              CASE
                WHEN dv.internaldata IS NULL THEN '{}'
                WHEN dv.internaldata::json->'officePlan' IS NULL THEN '{}'
                ELSE dv.internaldata::json->'officePlan'-> extract(week FROM current_date) + 1
              END as officeplan,

Is it even possible to do it this way? Is there another, better approach?

Upvotes: 0

Views: 202

Answers (1)

user330315
user330315

Reputation:

You need to convert the result of the extract to a text value

SELECT dv.unitid as id,
       CASE
         WHEN dv.internaldata IS NULL THEN '{}'
         WHEN dv.internaldata::json -> 'officePlan' IS NULL THEN '{}'
         ELSE dv.internaldata::json -> 'officePlan' -> (extract(week FROM current_date) + 1)::text
       END as officeplan,

But you don't really need the case expression to begin with, you can simplify that with a coalesce() 

coalesce(dv.internaldata -> 'officePlan' -> (extract(week FROM current_date) + 1)::text, '{}')

Upvotes: 3

Related Questions