How to fetch just the 'value' from a result set of Key value pair (dict) in Azure

[vwake7]

@jeffreyaven How to fetch just the 'value' from a dict result set in Azure. I need to join it to other tables.

stackql = StackQL(output='dict') query = """ select name from azure.compute.virtual_machine_scale_sets where subscriptionId = '%s' and resourceGroupName = 'vmss-flex'; """ % (subscription_id) res = stackql.execute(query) print(res)

Result is [{'name': 'vmss1'}]

I just need - vmss1.
name.data or name.value does not work.

I tried output = 'csv' and was able to get vmss1, but it was adding extra quotes to other json fields.
Any help is appreciated.

[jeffreyaven]

hey @vwake7

to get the value from a dictionary use the code below:

stackql = StackQL(output='dict') 
query = """  select  name from azure.compute.virtual_machine_scale_sets where subscriptionId = '%s' and resourceGroupName = 'vmss-flex';  """ % (subscription_id) 
res = stackql.execute(query)[0]['name']  
print(res)
# or you could do
res = stackql.execute(query)
vmss_name = res[0]['name']
print(vmss_name)

this should work, result sets from execute are always lists of dictionary objects, in your case there is only one row returned so you juet get the first one using the 0 index, then get the field name using the output column name, in your case name. dict is the default by the way as well. Let me know if this helps.

[vwake7]

@jeffreyaven thanks for responding, I am trying to do a join with another table. So I am able to join using 'csv' as in below code. But want to check if I can do the same with 'dict'.

The below join doesn't happen with 'dict'

a.name = b.virtualMachineScaleSetName

stackql = StackQL(output='csv')
query = """
select  c.properties
from
  azure.compute.virtual_machine_scale_sets a
  inner join  azure.compute.virtual_machine_scale_set_vms b
    on a.name = b.virtualMachineScaleSetName
  left outer join  azure.compute.virtual_machines c
    on b.name = c.name
where subscriptionId = '%s'
and resourceGroupName = '%s'
;""" % (subscription_id,rg)
res = stackql.execute(query)
print(res)

[jeffreyaven]

what's the error you are getting with this @vwake7 the output format shouldn't affect this

[vwake7]

@jeffreyaven when I run the same sql with 'dict', I don't see anything in the result set. But when I use 'csv'. I can see the properties of all the vms.

[jeffreyaven]

thats strange... properties should be an object as well, I may need to try an reproduce, can you tell me what version of pystackql and stackql you are running, you can get this using stackql.package_version and stackql.version respectively

[vwake7]

@jeffreyaven , it works as you said, I must have overlooked something. So output='dict' or 'csv' is just to format the result set and not the internal representation of data, is it.

[jeffreyaven]

Correct @vwake7 , output is simply the format