Find data from documets wich have keys which I chose from another select

SQL++
,
1

I have a document for example:

    data:{
       "en": {
          "booksSelect": [
            "fddc38be-728c-4858-9bc6-6e63557f2758",
            "d2a98652-cc7b-465d-ab48-be86b077e638"
          ],
          "textField": "text EN",
          "Layer2": [
            {
              "booksSelect": "fddc38be-728c-4858-9bc6-6e63557f2758",
              "carSelect": "11dc11be-711c-1158-11c6-6e63557f2111"
            }
          ]
    }

I receive data from another request  that contains resalts   as 

  [
      "booksSelect",
      "carSelect"
    ]

I need to select from another bukets (second query ) data by keys from first query: “booksSelect”, “carSelect”

OR combine them into one

How can I do it?

2

Your question is not clear. you have multiple booksSelect which one you want.
Give sample documents and expected output.

SELECT d, r
FROM default AS d
JOIN default AS r  ON KEYS  d.data.en.biiksSelect 
WHERE .....;
3

I have Query wich select some Datas from backet . It return [
“booksSelect”,
“carSelect”
]
I need to build a qury wich returning a values from document by KEYS “booksSelect”, “carSelect” , they can be in any deep in documents.

In ths example :
“booksSelect”: [
“fddc38be-728c-4858-9bc6-6e63557f2758”,
“d2a98652-cc7b-465d-ab48-be86b077e638”
]

AND

“booksSelect”: “fddc38be-728c-4858-9bc6-6e63557f2758”,
“carSelect”: “11dc11be-711c-1158-11c6-6e63557f2111”

4

6.5 AND above

   SELECT d2.* 
    FROM (WITH Q1 AS (......add your subquery......)
          SELECT ARRAY_FLATTEN(ARRAY (ARRAY v.[f] FOR v  WITHIN {d} WHEN  v.[f] IS NOT MISSING END) FOR f IN Q1 END,2) AS  ks
          FROM  default d
          WHERE ANY f IN Q1  SATISFIES ( ANY v  WITHIN {d}  SATISFIES  v.[f] IS NOT MISSING END) END) AS d1
    JOIN default AS d2 ON KEYS d1.ks;

ELSE

   SELECT d2.* 
    FROM (
          SELECT ARRAY_FLATTEN(ARRAY (ARRAY v.[f] FOR v  WITHIN {d} WHEN  v.[f] IS NOT MISSING END) FOR f IN Q1 END,2) AS  ks
          FROM  default d
          LET Q1 =  (......add your subquery......)
          WHERE ANY f IN Q1  SATISFIES ( ANY v  WITHIN {d}  SATISFIES  v.[f] IS NOT MISSING END) END) AS d1
    JOIN default AS d2 ON KEYS d1.ks;
5

Thanks a lot.
Please help me another
I need to replace ID on its documents

For exaple now
data:{
“en”: {
“booksSelect”: [
“fddc38be-728c-4858-9bc6-6e63557f2758”,
“d2a98652-cc7b-465d-ab48-be86b077e638”
],

result must be

data:{
“en”: {
“booksSelect”: [
{
id: “fddc38be-728c-4858-9bc6-6e63557f2758”, “data”: “test YYYYY”
},
{
id: “d2a98652-cc7b-465d-ab48-be86b077e638”, “data”: " test XXXX"
}
],
Can i do it in one query?

6

if path is not known it is not possible

7

please tell me if it is possible to find a path by value for example value is fddc38be-728c-4858-9bc6-6e63557f2758,
path will by data.en.booksSelect

OR

I can replace ID fddc38be-728c-4858-9bc6-6e63557f2758 to the json
{
id: “fddc38be-728c-4858-9bc6-6e63557f2758”, “data”: “test YYYYY”
} as string may by ?

8

path by value is not possible.

If you already know the path

SELECT d.*, OBJECT_PUT(d.data,"en",OBJECT_PUT(d.data.en,"booksSelect"bs)) AS data
FROM default AS d
LET bs = (SELECT MTEA(b).id, b AS data 
                      FROM default AS b USE KEYS d.data.en.booksSelect)
WHERE ...........;