Replace selectable field with a filtered version

SQL++
,
1

Consider the following document:

{
    "id": "1bd5a092-836c-4b6b-a355-e39f9647d397",
    "content": {
        "fruits": [
            {
                "text": "apples",
                "shop_id": "0a04c659-682e-4049-b710-f47ebbd884cd"
            },
            {
                "text": "bananas",
                "shop_id": "d757cabc-728e-466d-8c92-ca6c05bd3412"
            }
        ],
        "materials": [
            ···
        ]
    }
}

What I am trying to do is replace the content field within the query’s context such that the SELECT and WHERE clauses go against the replaced object.

The use case for this would be filtering content such that both fruits, materials, ... only contain objects having a specific shop_id value. Then the idea is that WHERE clauses for the content field only work for the shop-specific objects.

This is because we are generating filters dynamically so that the user can filter the data freely, but we want to limit their queries on a per-shop basis. And there is no practical way for us to inject the shop_id match clause onto the user-provided filter - hence the need to first have a shop-limited content and then apply the filter on it.

2

Not sure if your question.

If user want to specific show id you can write query like this.

SELECT d.*, { "fruits": ARRAY f FOR f IN d.content.friuts WHEN f.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd" END,
                             "materials": ARRAY m FOR m IN d.content.friuts WHEN m.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd" END} AS content
FROM default AS d
WHERE ANY f IN d.content.friuts SATISFIES f.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd" END
                    OR ANY m IN d.contet.materials SATISFIES m.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd" END

OR

SELECT u.*
FROM default AS d
UNNEST ARRAY_CONCAT((SELECT RAW f FROM d.content.friuts AS f WHERE f.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd"),
                    (SELECT RAW m FROM d.content.materials AS m WHERE m.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd")) AS u

OR

SELECT u.*
FROM default AS d
UNNEST (ARRAY v FOR v WITHIN d.content WHEN v.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd" END) AS u

If you still have question post the materials portion of the document and post the expected output.

3

Let me be more specific. Consider the following document:

{
    "id": "1bd5a092-836c-4b6b-a355-e39f9647d397",
    "content": {
        "fruits": [
            {
                "text": "apples",
                "shop_id": "0a04c659-682e-4049-b710-f47ebbd884cd"
            },
            {
                "text": "bananas",
                "shop_id": "d757cabc-728e-466d-8c92-ca6c05bd3412"
            }
        ],
        "materials": [
            {
                "number": 30,
                "shop_id": "0a04c659-682e-4049-b710-f47ebbd884cd",
            },
            {
                "number": 50,
                "shop_id": "d757cabc-728e-466d-8c92-ca6c05bd3412",
            }
        ]
    }
}

Then consider the following conceptual query:

SELECT content
FROM bucket
LET content = (
    ···
)
WHERE ANY material IN content.materials SATISFIES
    material.number > 40
END

Where LET content is replacing content with the following object:

{
        "fruits": [
            {
                "text": "apples",
                "shop_id": "0a04c659-682e-4049-b710-f47ebbd884cd"
            }
        ],
        "materials": [
            {
                "number": 30,
                "shop_id": "0a04c659-682e-4049-b710-f47ebbd884cd",
            }
        ]
}

So that it only contains objects that have a shop_id field equal to 0a04c659-682e-4049-b710-f47ebbd884cd.

This query would not return the defined document, because the material object for this shop does not satisfy number > 30.

This way I do not have to filter by shop_id within the ANY clause, because the new content object is already filtered.

4 
SELECT content
FROM bucket AS b
LET content = {
              "friuts" : (SELECT RAW f FROM b.content.friuts AS f WHERE f.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd"),
              "materials" : (SELECT RAW m FROM b.content.materials AS m WHERE m.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd")
              }
)
WHERE ANY material IN content.materials SATISFIES
    material.number > 40
END
5

Thing is I do not know the fields the content object has.

They are dynamic. So I need a way to generate those clauses automatically.

6 
SELECT content
FROM `bucket` AS b
LET content = (OBJECT o.name:o.val
              FOR o IN (ARRAY {v.name ,
                               "val": (SELECT RAW f
                                       FROM b.content.[v.name] AS f
                                       WHERE f.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd")
                              }
                        FOR v IN OBJECT_PAIRS(b.content) END)
                 WHEN o.val != []
                END)
WHERE ANY material IN content.materials SATISFIES
    material.`number` > 40
END
7

Much appreciated so far. Would it be possible to replace b.content? I.e. the aliased field?

8

You can replace any field with source of the document.

9

I cannot do LET b.content = however. That’s what I meant :thinking:

10

You can’t override source object.
The above query has once source of document b
Then let clause did content = …
Left side become second source, right side can uses first source.
Rest of the query has two sources b , content.

If you want use derived source use content. If you want original one use b.content

SELECT newcontnent
FROM `bucket` AS b
LET newcontent = (OBJECT o.name:o.val
              FOR o IN (ARRAY {v.name ,
                               "val": (SELECT RAW f
                                       FROM b.content.[v.name] AS f
                                       WHERE f.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd")
                              }
                        FOR v IN OBJECT_PAIRS(b.content) END)
                 WHEN o.val != []
                END)
WHERE ANY material IN newcontent.materials SATISFIES
    material.`number` > 40
END
11

Would it not be possible to do LET b and replace b with a new object whose content field is the new one?

Something like LET b = {"content": newcontent, ...b}

12

You have b you can do what ever you want.

SELECT b.*, newcontnent AS content
FROM `bucket` AS b
LET newcontent = (OBJECT o.name:o.val
              FOR o IN (ARRAY {v.name ,
                               "val": (SELECT RAW f
                                       FROM b.content.[v.name] AS f
                                       WHERE f.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd")
                              }
                        FOR v IN OBJECT_PAIRS(b.content) END)
                 WHEN o.val != []
                END)
WHERE ANY material IN newcontent.materials SATISFIES
    material.`number` > 40
END

Above query select all fields of b and then override content with specific to shop_id content

13

Is there no way to do LET b = OBJECT_PUT(b, "content", newcontent)?

14

Also would it be possible to filter both arrays and objects?

I.e. {"materials: [...], "name": {}}

Such that the name object is filtered out the same way if the shop_id does not match.

15

CASE cade let only there is next release and you can’t reuse or override variable in the scope again.
Use subquery and project so that original content will be replaced.

SELECT ..
FROM (SELECT b.*, (OBJECT o.name:o.val
                   FOR o IN (ARRAY {v.name ,
                                    "val": (SELECT RAW f
                                            FROM b.content.[v.name] AS f
                                            WHERE f.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd")
                                   }
                                   FOR v IN OBJECT_PAIRS(b.content)
                             END)
                   WHEN o.val != []
                   END)
      FROM `bucket` AS b
      ) AS b
WHERE ANY material IN b.content.materials SATISFIES
    material.`number` > 40
END;

Not sure what is name ?? ANY clause already filters out if materials is empty.

If you want you can try this also

SELECT ..
FROM (SELECT b.*, newcontect AS content
      FROM `bucket` AS b
      LET newcontect = (OBJECT o.name:o.val
                        FOR o IN (ARRAY {v.name ,
                                         "val": (SELECT RAW f
                                                 FROM b.content.[v.name] AS f
                                                 WHERE f.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd")
                                        }
                                        FOR v IN OBJECT_PAIRS(b.content)
                                  END)
                        WHEN o.val != []
                        END)
      WHERE newcontect.materials IS NOT NULL
      ) AS b
WHERE ANY material IN b.content.materials SATISFIES
    material.`number` > 40
END;
16 
INSERT INTO `bucket` VALUES ("k01", { "id": "1bd5a092-836c-4b6b-a355-e39f9647d397", "content": { "fruits": [ { "text": "apples", "shop_id": "0a04c659-682e-4049-b710-f47ebbd884cd" }, { "text": "bananas", "shop_id": "d757cabc-728e-466d-8c92-ca6c05bd3412" } ], "materials": [ { "number": 30, "shop_id": "0a04c659-682e-4049-b710-f47ebbd884cd" }, { "number": 50, "shop_id": "d757cabc-728e-466d-8c92-ca6c05bd3412" } ] } });

CREATE INDEX ix1 ON `bucket` (DISTINCT ARRAY v.shop_id FOR v WITHIN content END );

SELECT b.*
FROM (SELECT b.*, newcontect AS content
      FROM `bucket` AS b
      LET newcontect = (OBJECT o.name:o.val
                        FOR o IN (ARRAY {v.name ,
                                         "val": (SELECT RAW f
                                                 FROM v.val AS f
                                                 WHERE f.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd")
                                        }
                                        FOR v IN OBJECT_PAIRS(b.content)
                                  END)
                        WHEN o.val != []
                        END)
      WHERE ANY v WITHIN b.content SATISFIES v.shop_id = "0a04c659-682e-4049-b710-f47ebbd884cd" END AND newcontect != {}
      ) AS b
WHERE ANY material IN b.content.materials SATISFIES material.`number` > 20 END;