How to query documents that look like this?

SQL++
1

I have a document that looks like this:

{
  "type": "audit",
  "audit_details": {
    "playbook_name": "upgrade_all_web_servers",
    "rt_ticket_number": "16123",
    "play_date": "2016-03-16",
    "status": "success"
  },
  "affected_servers": {
    "server1.mydomain.net": {
      "package1": {
        "version_after_update": "x.x",
        "version_before_update": "x.x",
        "last_updated_on": "2016-03-16"
      },
      "package2": {
        "files": {
          "file_name": "my.cfg",
          "version_after_update": "x.x",
          "version_before_update": "x.x",
          "last_updated_on": "2016-03-16"
        }
      }
    },
    "server1.notmydomain.net": {
      "package3": {
        "version_after_update": "x.x",
        "version_before_update": "x.x",
        "last_updated_on": "2016-03-16"
      },
      "package4": {
        "files": {
          "file_name": "test.ini",
          "version_after_update": "x.x",
          "version_before_update": "x.x",
          "last_updated_on": "2016-03-16"
        }
      }
    }
  }
}

I’m going to potentially have hundreds of these types of documents (saved as “audit_details_XX”.
How could I write a N1QL query that would find all audit docs that have affected servers in the “mydomain.net” domain?
So in this case, I want find the information nested under the server1.mydomain.net object.

“server1.mydomain.net”: {
“package1”: {
“version_after_update”: “x.x”,
“version_before_update”: “x.x”,
“last_updated_on”: “2016-03-16”
},

If you think I’ve organized my document in a complicated manner, please let me know. But the idea is that on a given date, a script will be run that will update many servers. I want to capture the output / results of the script in one document. But I also then need to provide a way for users to query update details by server name or location.

Thanks

2

All of this is doable without changing your documents or organization. We just need more details. Your document above is not proper JSON. First, you should clarify which things are arrays vs. objects. After that, tell us exactly what questions you want to answer from your data, i.e. which queries you want to run. if you provide the questions in English, we can help translate to N1QL. Hope this helps. -Gerald

3

@geraldss thanks for taking the time to respond. I now see what you mean about arrays vs. objects. I’ve fixed my document to look like this:

{
	"type": "audit",
	"audit_details": {
		"playbook_name": "upgrade_all_web_servers",
		"rt_ticket_number": "16123",
		"play_date": "2016-03-16",
		"status": "success"
	},
	"affected_servers": [{
		"server1.mydomain.net": [{
			"package1": {
				"version_after_update": "x.x",
				"version_before_update": "x.x",
				"last_updated_on": "2016-03-16"
			},
			"package2": {
				"files": {
					"file_name": "my.cfg",
					"version_after_update": "x.x",
					"version_before_update": "x.x",
					"last_updated_on": "2016-03-16"
				}
			}
		}],
		"server1.anotherdomain.net": [{
			"package3": {
				"version_after_update": "x.x",
				"version_before_update": "x.x",
				"last_updated_on": "2016-03-16"
			},
			"package4": {
				"files": {
					"file_name": "another.cfg",
					"version_after_update": "x.x",
					"version_before_update": "x.x",
					"last_updated_on": "2016-03-16"
				}
			}
		}]
	}]
}

Now as far as what I’m after, I would like to query the database using a N1QL query to find all documents that have “server1.mydomain.net” in the list of affected_servers, and be able to display the list of packages for server1.mydomain.net
How would I do that?

Thanks in advance for your time and assistance!

4

You can do the following:

SELECT afs.`server1.mydomain.net` AS s
FROM my_bucket AS b
UNNEST b.affected_servers AS afs
WHERE afs.`server1.mydomain.net` IS NOT NULL;