Ask if value is contained in list of objects

SQL++
1

I have a user object that contains an array of objects like this:

  "fisheryinterests": [
    {
      "daysharbour": 3,
      "fishedsince": 1975,
      "dayslake": 0,
      "daysstream": 3,
      "fisherytype": "1",
      "daysputtake": 0,
      "dayssea": 0
    },
    {
      "fisherytype": "2"
    },
    {
      "fisherytype": "3"
    }
  ],

I would like to query if the user is fishery type 1,2 and/or 3 (and eventually replace that value with a “name”).

I have fiddled a little with this query that “works” (with some more attributes):

SELECT t2.name, t1.`key`,t1.refishstartdate,
t1.refishstopdate,
t1.yearborn,
t2.email,
ifmissingornull(t2.cellphone,ifmissingornull(t2.phone,'-')) as phone,
replace(replace(replace(replace(ENCODE_JSON(t1.fisheryinterests[*].fisherytype),'null',''),'\"',''),'[',''),']','') fisherytypes
FROM data AS t1
LEFT JOIN data AS t2 ON 'User:Private:' || t1.`key` = META(t2).id AND t2.type='Private' 
WHERE t1.type='User' AND t1.refishstartdate is valued

It just seems a little “clumsy”. If I want to replace the “types” with real names I would have to add another 3 replace calls :slight_smile:
Ideally, I would just like to “flatten” the fishery type field as a value and then query if it contains e.g. “1” and return “Angling” etc.
Can I do that to make it more “elegant”?

2 
ARRAY CASE WHEN v.fisherytype IN ["1","2","3"] TEHN "name" ELSE v.fisherytype END 
          FOR v IN t1.fisheryinterests 
WHEN v.fisherytype IS NOT NULL END
1 Like
3

Elegant, thank you! :wink::+1: