1

I have a couchbase document like :

{
  "data": {
    "author1": {
      "title": [
        "1",
        "2"
      ]
    },
    "author2": {
      "title": [
        "3",
        "4"
      ]
    }
  }
}

And I would like to get with a N1QL request :

1
2
3
4

How is it possible ?

Mo0nKizz
  • 149
  • 7
  • Is it always author1 and author2, or will the number of authors vary (e.g. author3, author4 in some documents)? – Matthew Groves Feb 24 '22 at 14:58
  • 1
    yes it can vary, for example author-3, author-abc and some on. And I can have multiple authors, not only 2. – Mo0nKizz Feb 24 '22 at 14:59
  • Oh, so it's not even a regular naming convention? it could be "author1" followed by "author-2" and then "author-A", for instance? Do they at least always start with "author"? – Matthew Groves Feb 24 '22 at 15:08
  • 1
    Yes they will always begin with a pattern like author – Mo0nKizz Feb 24 '22 at 15:12

2 Answers2

1

This might work. It assumes that title contains the values you want.

select VALUE ARRAY_FLATTEN(OBJECT_VALUES(OBJECT_UNWRAP(b))[*].title,1)
from books b
  • OBJECT_UNWRAP gets rid of the data and unwraps to peel away a layer.
  • OBJECT_VALUES then returns the values of the individual fields (whatever they are: author1, authorA, author-123)
  • The [*].title syntax returns every title field in the resulting array
  • ARRAY_FLATTEN combines the arrays
  • Finally, use VALUE to get the raw values.

I am NOT claiming this is the best approach, but it seems to work okay with the small sample data set (of 2 docs) that I created based on your question.

Matthew Groves
  • 25,181
  • 9
  • 71
  • 121
  • Yes thank you it works ! But if I have : { data : {.....}, date : "" } I think it will change the request because OBJECT_UNWRAP will not work – Mo0nKizz Feb 24 '22 at 15:56
  • You're right. But I'd recommend checking out the OBJECT_ and ARRAY_ functions and even dig into the SELECT syntax; you might be able to find a combination that works for you. – Matthew Groves Feb 24 '22 at 16:24
1
SELECT ARRAY_FLATTEN(ARRAY v.title FOR n:v IN b.data END,1) AS title
FROM books b;

OR

SELECT ARRAY_FLATTEN(OBJECT_VALUES(b.data)[*].title,1) AS title
FROM books b;

If need across the documents

WITH books AS ([ { "data": { "author1": { "title": [ "1", "2" ] }, "author2": { "title": [ "3", "4" ] } } },
                { "data": { "author1": { "title": [ "1", "5" ] }, "author2": { "title": [ "6", "1" ] } } }
              ])
SELECT DISTINCT RAW t
FROM books AS b
UNNEST ARRAY_FLATTEN(ARRAY v.title FOR n:v IN b.data END,1) AS t;

FOR n:v IN b.data

Looping construct

If b.data is OBJECT n holds field name, v holds value
If b.data is ARRAY  n holds position, v holds value
vsr
  • 7,149
  • 1
  • 11
  • 10