Query "Last updated" from nested entity?

SQL++
1

I have this basic query:

SELECT *
FROM data
WHERE Name LIKE "%Doe%"

The bucket name is data - and it returns this test data:

[
  {
    "data": {
      "Closed": true,
      "Date": "2017-02-24T16:59:41+0100",
      "Email": "john.doe@gmail.com",
      "Heading": "Test af feedback...",
      "Key": "4893D459F92BAEAEC12580D10057DCAF",
      "Message": "Hej!",
      "Name": "Mr. Doe",
      "RelatedUrl": "clubadmin.xsp",
      "RevisionInfo": {
        "Created": "2017-02-24T16:59:41+0100",
        "CreatedBy": "John Dalsgaard/BA171123846CEBF1C1257CB2002DA330/Fangst",
        "Updates": [
          {
            "Modified": "2018-04-16T13:17:49+0200",
            "ModifiedBy": "John Dalsgaard/BA171123846CEBF1C1257CB2002DA330/Fangst"
          }
        ]
      },
      "Subject": "9",
      "Type": "Feedback",
      "Unid": "4893D459F92BAEAEC12580D10057DCAF",
      "UserKey": "BA171123846CEBF1C1257CB2002DA330"
    }
  },
  {
    "data": {
      "Closed": true,
      "Date": "2017-06-19T15:18:28+0200",
      "Email": "john@doe.com",
      "Heading": "Test",
      "Key": "539B0D9C73696D47C125814400492045",
      "Message": "... fra app :-)",
      "Name": "John Doe",
      "RelatedUrl": "Fra mobil app",
      "RevisionInfo": {
        "Created": "2017-06-19T15:18:44+0200",
        "CreatedBy": "anonymous",
        "Updates": [
          {
            "Modified": "2018-04-16T15:44:51+0200",
            "ModifiedBy": "John Dalsgaard/BA171123846CEBF1C1257CB2002DA330/Fangst"
          },
          {
            "Modified": "2018-04-16T15:45:10+0200",
            "ModifiedBy": "John Dalsgaard/BA171123846CEBF1C1257CB2002DA330/Fangst"
          }
        ]
      },
      "Subject": "9",
      "Type": "Feedback",
      "Unid": "539B0D9C73696D47C125814400492045",
      "UserKey": "587CE5200641ABD9C1257E500051DDCD"
    }
  },
  {
    "data": {
      "Closed": false,
      "Date": "2016-05-05T22:26:07+0200",
      "Email": "j.doe@post.cybercity.dk",
      "Heading": "Login",
      "Key": "46A8A36707A745BAC1257FAA007040DF",
      "Message": "Hej\n\nJeg kan ikke logge ind selvom jeg bruger det korrekte kodeord og den mail jeg er oprettet med? Kan i hjælpe mig?\n\nVenligst\n\nSteffen Skovgaard",
      "Name": "Mrs. Doe",
      "RelatedUrl": "index.xsp",
      "RevisionInfo": {
        "Created": "2016-05-05T22:26:07+0200",
        "CreatedBy": "anonymous"
      },
      "Subject": "9",
      "Type": "Feedback",
      "Unid": "CFF25D38E4BB7A72C1258271004C42DE"
    }
  }
]

It still confuses me a little that the name of the bucket is returned as part of the result (and that I have to use that when I process the returned rows in the Java SDK) - but that is another thing :slight_smile:

I have tried to unnest the RevisionInfo node of the above dataset. But i cannot find the right syntax as it returns an empty result. I think something like this ought to give me something:

SELECT *
FROM data
UNNEST RevisionInfo

or

SELECT *
FROM data
UNNEST data.RevisionInfo

and I have also tried to use the aliases:

SELECT r.*
FROM data d
UNNEST d.RevisionInfo r

What am I doing wrong here??? I have just created on primary index - but as I understand it I should still be able to use unnest anyway.

The real query that I am trying to build is one that will return me the latest Created or Modified of the entire dataset.

I am new to N1QL so still struggling a little with the best approach to doing things - and next how to do it most efficient. I’m using the Community Server version 5.0.1

I am trying this out using the Query Editor on the builtin web page of the Couchbase server on port 8091 :wink:

2

If you don’t want the bucket name you need to select <bucketname>.* or <alias>.*

SELECT d.*
    FROM data AS d
    WHERE d.Name LIKE "%Doe%"
    ORDER BY d.RevisionInfo.Created DESC;

UNNEST can be used on ARRAY, RevisionInfo is not array. If you need to access Created
Use RevisionInfo.Created
UNNEST is self join of original document with array elements, If Unnest is not array join will not result any rows.

SELECT d.*
FROM data AS d
UNNEST TO_ARRAY(IFMISSINGORNULL(d.RevisionInfo.Updates,{"Modified":d.RevisionInfo.Created, "ModifiedBy":d.RevisionInfo.CreatedBy})) AS du
WHERE d.Name LIKE "%Doe%"
ORDER BY du.Modified DESC;

Second Edition book has some important articles on LIKE and how to design index
https://blog.couchbase.com/a-guide-to-n1ql-features-in-couchbase-5-5-special-edition/

https://blog.couchbase.com/working-json-arrays-n1ql/

3

Thanks, I really like the idea of this! - however, it returns an empty result set… :face_with_raised_eyebrow:
I have tried to wrestle it a little - but not found the solution yet…

Oh, and I did see the reference to the n1ql features in special edition 5.5 and therefore didn’t look at it as I am running on the Community Server 5.0.1. Does it still apply for this version?

4

This is the EXPLAIN output of the statement (without the WHERE... clause):

{
  "plan": {
    "#operator": "Sequence",
    "~children": [
      {
        "#operator": "PrimaryScan",
        "index": "def_primary",
        "keyspace": "data",
        "namespace": "default",
        "using": "gsi"
      },
      {
        "#operator": "Fetch",
        "as": "d",
        "keyspace": "data",
        "namespace": "default"
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "Unnest",
              "as": "du",
              "expr": "ifmissingornull(to_array(((`d`.`RevisionInfo`).`Updated`)), {\"Modified\": ((`d`.`RevisionInfo`).`Created`), \"ModifiedBy\": ((`d`.`RevisionInfo`).`CreatedBy`)})"
            }
          ]
        }
      },
      {
        "#operator": "Parallel",
        "~child": {
          "#operator": "Sequence",
          "~children": [
            {
              "#operator": "InitialProject",
              "result_terms": [
                {
                  "expr": "`d`",
                  "star": true
                }
              ]
            },
            {
              "#operator": "FinalProject"
            }
          ]
        }
      }
    ]
  },
  "text": "SELECT d.*\nFROM data AS d\nUNNEST IFMISSINGORNULL(TO_ARRAY(d.RevisionInfo.Updated),{\"Modified\":d.RevisionInfo.Created, \"ModifiedBy\":d.RevisionInfo.CreatedBy}) AS du"
}

… perhaps it gives someone else a better idea of what’s wrong?

I see in other examples that people often have #primary for the primary index. Is that a naming convention - or because it was created by the server? I created my index manually, hence the name def_primary:wink:

5

There is typo Updates and TO_ARRAY needs to be outside

 SELECT d.*
FROM data AS d
UNNEST TO_ARRAY(IFMISSINGORNULL(d.RevisionInfo.Updates,{"Modified":d.RevisionInfo.Created, "ModifiedBy":d.RevisionInfo.CreatedBy})) AS du
WHERE d.Name LIKE "%Doe%"
ORDER BY du.Modified DESC;

There are 2 books Special Edition applicable only 5.5, second edition applicable 5.0, 5.5 ,…

if you don’t give name to primary index it uses #primary

If you are expecting latest updated document across result set
(For further optimization and explanation check Select non-group by fields using sql query)

SELECT RAW MAX([(SELECT RAW MAX(du.Modified) FROM IFMISSINGORNULL(d.RevisionInfo.Updates,{"Modified":d.RevisionInfo.Created, "ModifiedBy":d.RevisionInfo.CreatedBy}) AS du)[0],d])[1]
FROM data AS d
WHERE d.Name LIKE "%Doe%";
6

Thanks , that did the trick!

I’ll have a look at the alternative that you specified. Would you consider that faster? Or better in other ways? I’ll definitely need to look at the optimization notes you refer to - still a newbie with Couchbase on this,

And I’ll find the “second exition” relevant to 5.0 for further reading :+1:

Thanks for your help!

7

… Can’t get this to work! It says there is a syntax error:

[
  {
    "code": 3000,
    "msg": "syntax error - at )",
    "query_from_user": "SELECT RAW MAX([(SELECT RAW MAX(du.Modified) FROM IFMISSINGORNULL(d.RevisionInfo.Updates,{\"Modified\":d.RevisionInfo.Created, \"ModifiedBy\":d.RevisionInfo.CreatedBy}) AS du)[0],d)[1]\nFROM data AS d\nWHERE d.Name LIKE \"%Doe%\";"
  }
]
8

Missing matching array bracket i.e ]

SELECT RAW MAX([(SELECT RAW MAX(du.Modified) 
            FROM IFMISSINGORNULL(d.RevisionInfo.Updates,
               {"Modified":d.RevisionInfo.Created, "ModifiedBy":d.RevisionInfo.CreatedBy}) AS du)[0],d])[1]
FROM data AS d
WHERE d.Name LIKE "%Doe%";
9

Bingo!

It seems just splitting it over the lines as you did made the difference :+1:

Thanks! - and sorry for the stupid questions :slight_smile:

10

Ah, no - there was a ] as difference! :+1: