| 2017-03-08 14:17:13 |
Thomas Maddox |
description |
We are currently unable to effectively query on nested variables for a resources. Not being able to do so severely hinders the power of this API and harms our value-add.
For example, let's say we want to query a series of hosts for which have a disk manufactured by Seagate and the data for this, expressed via variables on hosts, looks like:
{
"hardware_profiles": {
"disks": [
{
"manufacturer": "seagate",
"capacity": "2TB"
},
{
"manufacturer": "western",
"capacity": "5TB"
}
]
}
}
We wouldn't be able to effectively query for this condition with our existing minimal variable querying capabilities (simply matches for "key:value". After some discussion we've agreed that it'd be more expressive and easier to work with if we were to provide something like JSONPath queries to achieve this. So, the end-result would look something like:
craton host-list --vars 'hardware_profiles.disks[*].manufacturer:seagate'
There are a few of problems to be solved here:
1. How do we leverage our database's JSON capabilities to achieve querying variables this way?
2. How do we handle the existing data model where a variable can be either a String datatype or a JSON document?
3. How can this be applied to querying for resolved variables?
4. What limitations exist in SQLAlchemy that may prevent or hinder our ability to implement this, and how do we overcome them? |
We are currently unable to effectively query on nested variables for a resources. Not being able to do so severely hinders the power of this API and harms our value-add.
For example, let's say we want to query a series of hosts for which have a disk manufactured by Seagate and the data for this, expressed via variables on hosts, looks like:
{
"hardware_profiles": {
"disks": [
{
"manufacturer": "seagate",
"capacity": "2TB"
},
{
"manufacturer": "western",
"capacity": "5TB"
}
]
}
}
We wouldn't be able to effectively query for this condition with our existing minimal variable querying capabilities (simply matches for "key:value"). After some discussion, we've agreed that it'd be more expressive and easier to work with if we were to provide something like JSONPath queries to achieve this. So, the end-result would look something like:
craton host-list --vars 'hardware_profiles.disks[*].manufacturer:seagate'
There are a few of problems to be solved here:
1. How do we leverage our database's JSON capabilities to achieve querying variables this way?
2. How do we handle the existing data model where a variable can be either a String datatype or a JSON document?
3. How can this be applied to querying for resolved variables?
4. What limitations exist in SQLAlchemy that may prevent or hinder our ability to implement this, and how do we overcome them?
Workaround:
The interim solution for this lies in flattening the storage of the data to diminish the need for querying nested variables, but it's still not solved entirely, and certainly not in a sustainable way. |
|
| 2017-03-08 14:20:58 |
Thomas Maddox |
description |
We are currently unable to effectively query on nested variables for a resources. Not being able to do so severely hinders the power of this API and harms our value-add.
For example, let's say we want to query a series of hosts for which have a disk manufactured by Seagate and the data for this, expressed via variables on hosts, looks like:
{
"hardware_profiles": {
"disks": [
{
"manufacturer": "seagate",
"capacity": "2TB"
},
{
"manufacturer": "western",
"capacity": "5TB"
}
]
}
}
We wouldn't be able to effectively query for this condition with our existing minimal variable querying capabilities (simply matches for "key:value"). After some discussion, we've agreed that it'd be more expressive and easier to work with if we were to provide something like JSONPath queries to achieve this. So, the end-result would look something like:
craton host-list --vars 'hardware_profiles.disks[*].manufacturer:seagate'
There are a few of problems to be solved here:
1. How do we leverage our database's JSON capabilities to achieve querying variables this way?
2. How do we handle the existing data model where a variable can be either a String datatype or a JSON document?
3. How can this be applied to querying for resolved variables?
4. What limitations exist in SQLAlchemy that may prevent or hinder our ability to implement this, and how do we overcome them?
Workaround:
The interim solution for this lies in flattening the storage of the data to diminish the need for querying nested variables, but it's still not solved entirely, and certainly not in a sustainable way. |
We are currently unable to effectively query on nested variables for a resources. Not being able to do so severely hinders the power of this API and harms our value-add.
For example, let's say we want to query a series of hosts for which have a disk manufactured by Seagate and the data for this, expressed via variables on hosts, looks like:
{
"hardware_profiles": {
"disks": [
{
"manufacturer": "seagate",
"capacity": "2TB"
},
{
"manufacturer": "western",
"capacity": "5TB"
}
]
}
}
We wouldn't be able to effectively query for this condition with our existing minimal variable querying capabilities (simply matches for "key:value"). After some discussion, we've agreed that it'd be more expressive and easier to work with if we were to provide something like JSONPath queries to achieve this. So, the end-result would look something like:
craton host-list --vars 'hardware_profiles.disks[*].manufacturer:seagate'
There are several problems presented:
1. How do we leverage our database's JSON capabilities to achieve querying variables this way?
2. How do we handle the existing data model where a variable can be either a String datatype or a JSON document?
3. How can this be applied to querying for resolved variables?
4. What limitations exist in SQLAlchemy that may prevent or hinder our ability to implement this, and how do we overcome them?
5. In order to support this somewhat cleanly, we'll need to pin our DB requirement at MySQL 5.7, so this imposes a deployer burden.
Workaround:
The interim solution for this lies in flattening the storage of the data to diminish the need for querying nested variables, but it's still not solved entirely, and certainly not in a sustainable way. |
|
