We have JSON data stored in an Azure CosmosDB container like below:
[
{
"WebId": "99999996_2_10_125064",
"SectionId": 1551,
"rowtype": 4,
"Fields": [
{
"FieldId": 8998,
"FieldValue": "1002100481",
"FieldName": "<p>Project_No</p>8998"
},
{
"FieldId": 9839,
"FieldValue": "Customer Name 123",
"FieldName": "<p>Customer_Name</p>9839"
}
]
},
{
"WebId": "99999996_2_10_125064",
"SectionId": 1552,
"rowtype": 4,
"Fields": [
{
"FieldId": 13362,
"FieldValue": "76215_1_9_122345",
"FieldName": "<p>LW_WebID</p>13362"
},
{
"FieldId": 10369,
"FieldValue": null,
"FieldName": "<p>TRM_ID</p>10369"
}
]
}
],
[
{
"WebId": "99999996_2_10_125070",
"SectionId": 1551,
"rowtype": 4,
"Fields": [
{
"FieldId": 8998,
"FieldValue": "1002100482",
"FieldName": "<p>Project_No</p>8998"
},
{
"FieldId": 9839,
"FieldValue": "Customer Name 124",
"FieldName": "<p>Customer_Name</p>9839"
}
]
},
{
"WebId": "99999996_2_10_125070",
"SectionId": 1552,
"rowtype": 4,
"Fields": [
{
"FieldId": 13362,
"FieldValue": "94215_1_9_122346",
"FieldName": "<p>LW_WebID</p>13362"
},
{
"FieldId": 10369,
"FieldValue": null,
"FieldName": "<p>TRM_ID</p>10369"
}
]
}
]
Is it possible to get all WebIDs
where the FieldValue
for Fields.FieldName = <p>LW_WebID</p>13362
is Fields.FieldValue LIKE '76215_1_9_%'
? From the sample data, it should only get the records from WebId = '99999996_2_10_125064'
.
Currently, we are only able to pull all records that have the Field.FieldId
values 8998, 9839, 13362 and 10369
like below. Afterwards, we pivot the data in Power Query and filter the LW_WebId
but is it possible filter the data directly via the native query?
SELECT
c["WebId"]
, c["SectionId"]
, c["rowtype"]
, d["FieldId"]
, d["FieldName"]
, d["FieldValue"]
FROM c
JOIN d IN c["Fields"]
WHERE
c["WebId"] LIKE '99999996_2_10_%'
d["FieldId"] IN (13362, 8998, 9839)