Select column names and querying "LIKE" on Couchbase

Question

I want to get all column names from a bucket.

I found a query:

SELECT ARRAY_DISTINCT(ARRAY_AGG(v)) AS column
FROM mybucket b UNNEST object_names(b) AS v

It's getting column names array but I need LIKE SQL command. It's like this:

SELECT column
FROM mybucket
WHERE column LIKE '%test%'

Is there a way to do this?

Answer 1

This is a tricky one, depending on what you want the resultant structure to be. And disclaimer, there might be a more succinct way to do this (but I haven't found it yet--maybe there's another way that doesn't involve OBJECT_NAMES?).

But anyway, the key to this for me was the ARRAY collection operator.

For instance, this:

SELECT ARRAY a FOR a IN ARRAY_DISTINCT(ARRAY_AGG(allFieldNames))
       WHEN a LIKE '%test%' END AS filteredFieldNames
FROM mybucket b UNNEST object_names(b) AS allFieldNames

Will return results like

[
  {
    "filteredFieldNames": [
      "testField1",
      "anotherTestField"
    ]
  }
]

If you want a different format, you can work with the ARRAY operator expression. For instance:

SELECT ARRAY { "fieldName" : a } FOR a IN 
       ARRAY_DISTINCT(ARRAY_AGG(allFieldNames))
       WHEN a LIKE '%test%' END AS filteredFieldNames
FROM mybucket b UNNEST object_names(b) AS allFieldNames

Which would return:

[
  {
    "filteredFieldNames": [
      {
        "fieldName": "testField1"
      },
      {
        "fieldName": "anotherTestField"
      }
    ]
  }
]