Same Select, Different Results?

SQL++
1

The following two queries return results (using N1QL tutorial site) in a different order:

SELECT META().id, *
    FROM tutorial
        USE KEYS ["dave", "ian"]

SELECT META(e).id, e.*
    FROM tutorial e
        USE KEYS ["dave", "ian"]

As you can see, the second query just uses e as an alias.

I notice this because I’m chasing a bug in the .NET 2.7.1 client, where, using the same queries above (against a local database), I get different results from deserialization - but that’s another topic.

2

After doing more research, I understand the issue now - both with the difference between the two queries and the .NET client issue I was having.

For anyone else that sees this, due to the nature of JSON, SELECT * and SELECT .* do not return the same JSON as you might expect if familiar with SQL queries.

Select * (without any prefix in front of the asterisk), will wrap each returned item in a property such as:

    [
      {
        "mybucket": {
          "id": "1",
          "prop1_name": "prop1_value",
          "prop2_name": "prop2_value",
        }
      }
    ]

whereas prefixing the asterisk with the bucket name (SELECT bucket.*) yields:

    [
      {
    	  "id": "1",
    	  "prop1_name": "prop1_value",
    	  "prop2_name": "prop2_value",
      }
    ]

Coming from a SQL background, this was confusing to me. Hopefully it will help someone else.

1 Like