Try this:

 In[1]:= as = 
 Association[{x -> Association[{a -> 1, b -> 2}], 
   y -> Association[{a -> 2, b -> 2}], 
   z -> Association[{a -> 2, b -> 1}]}]

Out[1]= <|x -> <|a -> 1, b -> 2|>, y -> <|a -> 2, b -> 2|>, 
 z -> <|a -> 2, b -> 1|>|>

In[2]:= ds = Dataset[as]

Out[2]= Dataset[
Association[
 x -> Association[a -> 1, b -> 2], y -> Association[a -> 2, b -> 2], 
  z -> Association[a -> 2, b -> 1]], 
TypeSystem`Assoc[TypeSystem`AnyType, 
TypeSystem`Assoc[TypeSystem`AnyType, 
TypeSystem`Atom[Integer], 2], 3], 
Association["ID" -> 96684043424021]]

In[31]:= ds[Select[#[a] == 2 &]]

Out[31]= Dataset[
Association[
 y -> Association[a -> 2, b -> 2], z -> Association[a -> 2, b -> 1]], 
TypeSystem`Assoc[TypeSystem`AnyType, 
TypeSystem`Assoc[TypeSystem`AnyType, 
TypeSystem`Atom[Integer], 2], TypeSystem`AnyLength], 
Association["Origin" -> HoldComplete[
Query[
Select[#[a] == 2& ]][
Dataset`DatasetHandle[96684043424021]]], "ID" -> 246303524148496]]

The key to this is that the Select statement is acting at the first level, you are selecting keys at the first level based on a criterion within them, thus

ds[Select[...]]

after that, you want to pick based on a key inside the value, thus the

ds[Select[#[a]==2&]]

With #[a]==2&, the # refers to the value associated with the key in question (itself an Association) and the [a] is there to point to the value associated with the key of a.

Yes, if you are still picking Keys at top level, the Select statement stays on top. You just work your way down to the test you want to provide:

In[1]:= as = <|x -> <|a -> 1, b -> <|c -> 1, d -> 2|>|>, 
  y -> <|a -> 1, b -> <|c -> 2, d -> 2|>|>|>

Out[1]= <|x -> <|a -> 1, b -> <|c -> 1, d -> 2|>|>, 
 y -> <|a -> 1, b -> <|c -> 2, d -> 2|>|>|>

In[2]:= ds = Dataset[as]

Out[2]= Dataset[
Association[
 x -> Association[a -> 1, b -> Association[c -> 1, d -> 2]], 
  y -> Association[a -> 1, b -> Association[c -> 2, d -> 2]]], 
TypeSystem`Assoc[TypeSystem`AnyType, 
TypeSystem`Assoc[TypeSystem`AnyType, TypeSystem`AnyType, 2], 2], 
Association["ID" -> 168461536877204]]

In[3]:= ds[Select[#[b, c] == 2 &]]

Out[3]= Dataset[
Association[
 y -> Association[a -> 1, b -> Association[c -> 2, d -> 2]]], 
TypeSystem`Assoc[TypeSystem`AnyType, 
TypeSystem`Assoc[TypeSystem`AnyType, TypeSystem`AnyType, 2], 
  TypeSystem`AnyLength], 
Association["Origin" -> HoldComplete[
Query[
Select[#[b, c] == 2& ]][
Dataset`DatasetHandle[168461536877204]]], "ID" -> 216114199026345]]

This is just what I needed:

In[43]:= as2 = <|
   x -> <|a -> 1, b -> <|c -> 1, d -> <|e -> 4, f -> 5|>|>|>, 
   y -> <|a -> 1, b -> <|c -> 2, d -> <|e -> 4, f -> 6|>|>|>, 
   z -> <|a -> 2, b -> <|c -> 3, d -> <|e -> 5, f -> 6|>|>|>|>;

In[44]:= ds2 = Dataset[as2];


(* first level key only *)ds2[Select[#[a] == 2 &]] // Normal

Out[45]= <|z -> <|a -> 2, b -> <|c -> 3, d -> <|e -> 5, f -> 6|>|>|>|>


(* both first and second level keys *)
ds2[Select[#[a] == 1 && #[b, c] == 2 &]] // Normal

Out[46]= <|y -> <|a -> 1, b -> <|c -> 2, d -> <|e -> 4, f -> 6|>|>|>|>

Dear David,

just for me to understand: are you looking for a search pattern in Query that does essentially this?

dsQuery[ds_, key_, value_] := Normal[KeyTake[ds, Keys@Select[ds[[All, Key[key]]], # == value &]]]

If you use the example that you gave above:

as = Association[{x -> Association[{a -> 1, b -> 2}], y -> Association[{a -> 2, b -> 2}], z -> Association[{a -> 2, b -> 1}]}];
ds = Dataset[as];

the command

dsQuery[ds, a, 2]

i.e. in ds look for the entries for which the sub-key "a" has the value two, gives:

<|y -> <|a -> 2, b -> 2|>, z -> <|a -> 2, b -> 1|>|> 

which might be what you are looking for.

Is this what you want to achieve with the built in Query function?

Cheers,

M.