Where clause not working in Atlas sql

Saibal_Dutta · 2024-07-17T15:40:18.560Z

I have a json array as shown below, I am trying to query data with where clause in PowerBI, somehow the query is not working, any idea where am I going wrong?

"group_id": "843d01bf-e46e-41f8-e8694a3fc81c",
        "group_name": "[Customer] sample customer",
        "members": [
            {
                "asset_id": "3584b18c-9d85-3dd85ebed7b7",
                "asset_name": "sample asset",
                "long_name": "sample asset long name 1"
            },
            {
                "asset_id": "623feb17-afb2-02a79f534bed",
                "asset_name": "sample asset",
                "long_name": "sample asset long name 2"
            },
            {
                "asset_id": "a6b22d43-47fc-bde9-a16af0927ee4",
                "asset_name": "sample asset",
                "long_name": "sample asset long name3"
            }

sql query →
select * from FLATTEN(UNWIND(asset_groups WITH PATH => members, INDEX => idx)) Where members.long_name = ‘sample asset long name 1’

florence023 · 2024-07-18T09:49:58.535Z

[quote=“Saibal_Dutta, post:1, topic:288952, full:true”]choiceadvantage
I have a json array as shown below, I am trying to query data with where clause in PowerBI, somehow the query is not working, any idea where am I going wrong?

"group_id": "843d01bf-e46e-41f8-e8694a3fc81c",
        "group_name": "[Customer] sample customer",
        "members": [
            {
                "asset_id": "3584b18c-9d85-3dd85ebed7b7",
                "asset_name": "sample asset",
                "long_name": "sample asset long name 1"
            },
            {
                "asset_id": "623feb17-afb2-02a79f534bed",
                "asset_name": "sample asset",
                "long_name": "sample asset long name 2"
            },
            {
                "asset_id": "a6b22d43-47fc-bde9-a16af0927ee4",
                "asset_name": "sample asset",
                "long_name": "sample asset long name3"
            }

sql query →
select * from FLATTEN(UNWIND(asset_groups WITH PATH => members, INDEX => idx)) Where members.long_name = ‘sample asset long name 1’
[/quote]

Hello,
Let’s address your query in Power BI. To filter data based on the “long_name” field within the nested “members” array, you can follow these steps:

Data Transformation:
First, ensure that your JSON data is loaded into Power BI.
Go to the Power Query Editor (Home > Edit Queries).
Expand Nested Array:
Locate the column containing your JSON data (e.g., “Column1”).
Click the expand button next to the column header to expand the nested array (in your case, “members”).
Flatten the Data:
After expanding the array, you’ll see individual rows for each member.
Use the “Flatten” option to transform the data into a flat table.
Filter Rows:
Add a custom filter to the “long_name” column.
Set the filter condition to match the desired value (e.g., “sample asset long name 1”).
Applied Steps:
Your final query should look something like this:

let
    Source = ... (your original data source),
    ExpandedMembers = Table.ExpandListColumn(Source, "members"),
    FilteredRows = Table.SelectRows(ExpandedMembers, each [long_name] = "sample asset long name 1")
in
    FilteredRows

Load Data:
Click “Close & Apply” to load the filtered data into your Power BI report.
Remember to replace … with your actual data source and column names. The key steps are expanding the nested array and filtering the rows based on the “long_name” field.

Hope this work for you.
Best regards,
Florence Gayhart

Saibal_Dutta · 2024-07-20T11:44:17.116Z

Hi there,
Thanks for the reply, below code has resolved the issue.
select * from FLATTEN(UNWIND(asset_groups WITH PATH => members, INDEX => idx))
where “members_long_name” like ‘sample asset long name 1’

However, the above code dosn’t work with attached json structure, it throws below errors

Data

Here are the erroes in PowerBI

query1 → select * from FLATTEN(UNWIND(asset_group_members WITH PATH => members, INDEX => idx))

Error1 ->Error message: ODBC: ERROR [HY000] [MongoDB][Core] Trying to execute query failed with error: Kind: Command failed: Error code 96 (OperationFailed): failed getting result set schema: algebrize error: Error 1014: UNWIND INDEX name ‘idx’ conflicts with existing field name, correlationID = 17e38b0be957c7f8732844ef, labels: {}

Query 2 → select * from FLATTEN(UNWIND(asset_group_members WITH PATH => members))

Error 2 ->Error message: ODBC: ERROR [HY000] [MongoDB][Core] Trying to execute query failed with error: Kind: Command failed: Error code 96 (OperationFailed): failed getting result set schema: algebrize error: Error 3025: Insufficient schema information.
Caused by:
cannot exhaustively enumerate all field paths in schema Document(Document { keys: {“members”: Any}, required: {“members”}, additional_properties: true }), correlationID = 17e38b23d1e62f1d298df75a, labels: {}