How can I return a null or empty string if a key value does not exist?

SQL++
1

I am wondering if there is a way and if so what’s the best way if i query via n1ql and get a document that does not have all

lets say i have the following query where title and suffix might not be present in doc and if they are not i want to return an empty string, in case of history.created and history.updated i want to return a null if not present in doc. I tried the IFMISSING but had no luck

SELECT name.fname AS f_name,
 name.mname AS m_name,
 name.lname AS l_name,
 title AS title,
 suffix AS suffix,
 username AS user_name,
  _id AS guid,
  security.ADMIN AS ADMIN,
  security.developer AS developer,
  security.locked AS locked,
  history.created_on AS created_on,
  history.updated_on AS updated_on
  FROM Contacts
  WHERE _type = 'user'
  AND _id = $1`
2

IFMISSING should be what you need; something like:

SELECT name.fname AS f_name,
 name.mname AS m_name,
 name.lname AS l_name,
 IFMISSING(title,"") AS title,
 IFMISSING(suffix,"") AS suffix,
 username AS user_name,
  _id AS guid,
  security.ADMIN AS ADMIN,
  security.developer AS developer,
  security.locked AS locked,
  IFMISSING(history.created_on,NULL) AS created_on,
  IFMISSING(history.updated_on,NULL) AS updated_on
  FROM Contacts
  WHERE _type = 'user'
  AND _id = $1

HTH.

1 Like