Querying a map of maps with a dynamic key

Hi,
I seem to be stuck on this N1QL query.
consider I have this document with the key ‘fred-125’…

{
  "id": "fred-125",
  "type": "MD",
  "primaryOrders": {
    "HRRRE_mem0": {
      "cycle_seconds": "{\"0\":[0,32400],\"1520553600\":[0,43200,54000,64800],\"1522281600\":[0,43200,64800,75600],\"1530576000\":[0,43200],\"1552003200\":[0,43200,64800],\"1584403200\":[0,21600,43200,64800]}",
      "m_order": 439
    },
    "HRRRE_mem1": {
      "cycle_seconds": "{\"0\":[0,32400],\"1520553600\":[0,43200,54000,64800],\"1522281600\":[0,43200,64800,75600],\"1530576000\":[0,43200],\"1552003200\":[0,43200,64800],\"1584403200\":[0,21600,43200,64800]}",
      "m_order": 440
    },
    "HRRRE_mem2": {
      "cycle_seconds": "{\"0\":[0,32400],\"1520553600\":[0,43200,54000,64800],\"1522281600\":[0,43200,64800,75600],\"1530576000\":[0,43200],\"1552003200\":[0,43200,64800],\"1584403200\":[0,21600,43200,64800]}",
      "m_order": 450
    },
    "HRRRE_mem3": {
      "cycle_seconds": "{\"0\":[0,32400],\"1520553600\":[0,43200,54000,64800],\"1522281600\":[0,43200,64800,75600],\"1530576000\":[0,43200],\"1552003200\":[0,43200,64800],\"1584403200\":[0,21600,43200,64800]}",
      "m_order": 460
    },
    "HRRRE_mem4": {
      "cycle_seconds": "{\"0\":[0,32400],\"1520553600\":[0,43200,54000,64800],\"1522281600\":[0,43200,64800,75600],\"1530576000\":[0,43200],\"1552003200\":[0,43200,64800],\"1584403200\":[0,21600,43200,64800]}",
      "m_order": 470
    },
    "HRRRE_mem5": {
      "cycle_seconds": "{\"0\":[0,32400],\"1520553600\":[0,43200,54000,64800],\"1522281600\":[0,43200,64800,75600],\"1530576000\":[0,43200],\"1552003200\":[0,43200,64800],\"1584403200\":[0,21600,43200,64800]}",
      "m_order": 480
    },
    "HRRRE_mem6": {
      "cycle_seconds": "{\"0\":[0,32400],\"1520553600\":[0,43200,54000,64800],\"1522281600\":[0,43200,64800,75600],\"1530576000\":[0,43200],\"1552003200\":[0,43200,64800],\"1584403200\":[0,21600,43200,64800]}",
      "m_order": 490
    },
    "HRRRE_mem7": {
      "cycle_seconds": "{\"0\":[0,32400],\"1520553600\":[0,43200,54000,64800],\"1522281600\":[0,43200,64800,75600],\"1530576000\":[0,43200],\"1552003200\":[0,43200,64800],\"1584403200\":[0,21600,43200,64800]}",
      "m_order": 500
    }
  }
}

And I have a simple select for getting a key (in reality it is more complex but that doesn’t matter)…
select raw 'HRRRE_mem0' as k;
which gives me a k (‘HRRRE_mem0’)

I know I can do this…

select raw mdata.primaryOrders.HRRRE_mem0.m_order FROM mdata USE KEYS "fred-125";
[
  439
]

but how could I use the simple select that returns the key k ‘HRRRE_mem0’ such that I could replace the literal ‘HRRRE_mem0’ in the above query with the subquery so that I could implement the above query dynamically?

I’ve tried implementing a variable substitution in the query many different ways but I don’t seem to understand the proper syntax.
Thanks for any help.

@randy.pierce ,

WITH k AS ( "HRRRE_mem3")
SELECT RAW m.primaryOrders.[k].m_order
FROM mdata AS m USE KEYS "fred-125";

$k is query parameters

SELECT RAW m.primaryOrders.[$k].m_order
FROM mdata AS m USE KEYS "fred-125";


INSERT INTO default VALUES("f1", {"a":"b", "b":10});
SELECT  d.[d.a] AS val
FROM default AS d USE KEYS "f1";

This how map/dictionary access via N1QL
d.[d.a] ===> becomes d.[“b”] ===> d.b ===> returns value 10
As u see in syntax there array bracket after . and expression inside the array brackets
It evaluates expression in array brackets. It must evaluate to string
.[“b”] treats as if b is field name in the object.

Here from two keys construct object from original document

WITH ks AS ([ "HRRRE_mem3",   "HRRRE_mem4"])
SELECT RAW OBJECT v:m.primaryOrders.[v].m_order FOR v IN ks END
FROM mdata AS m USE KEYS "fred-125";

Complex example can be found here Merge into seems to not work correctly

Thank you so much! It was the . that was throwing me.
randy