How to map nested array items with N1QL?

Question

I have documents in a bucket called blocks in the following format:

{
  "random_field": 1,
  "transactions": [{
    "id": "CCCCC",
    "inputs": [{
      "tx_id": "AAAAA",
      "index": 0
    },{
      "tx_id": "BBBBB",
      "index": 1
    }]
  }]
}

{
  "transactions": [{
    "id": "AAAAA",
    "outputs": [{
      "field1": "value123",
      "field2": "value456"
    },{
      "field1": "ignore",
      "field2": "ignore"
    }]
  }]
}

{
  "transactions": [{
    "id": "BBBBB",
    "outputs": [{
      "field1": "ignored",
      "field2": "ignored"
    },{
      "field1": "value999",
      "field2": "value888"
    }]
  }]
}

and I need to map the inputs from the first document to the corresponding outputs of the second and third documents. The way to do it manually is to, for each input, find a transaction with id equal to the input's tx_id, and then get the item from the outputs array based on the index of the input. To exemplify, this is the object I would like to return in this scenario:

{
  "random_field": 1,
  "transactions": [{
    "id": "CCCCC",
    "inputs": [{
      "tx_id": "AAAAA",
      "index": 0,
      "output": {
        "field1": "value123",
        "field2": "value456"
      }
    },{
      "tx_id": "BBBBB",
      "index": 1,
      "output": {
        "field1": "value999",
        "field2": "value888"
      }
    }]
  }]
}

I managed to come up with the following query:

SELECT b.random_field,
  b.transactions -- how to map this?
FROM blocks b
UNNEST b.transactions t
UNNEST t.inputs input
JOIN blocks `source` ON (ANY tx IN `source`.transactions SATISFIES tx.`id` = input.tx_id END)
UNNEST `source`.transactions source_tx
UNNEST source_tx.outputs o
WHERE (ANY tx IN b.transactions SATISFIES tx.`id` = 'AAAAA' END) LIMIT 1;

I suppose there should be a way to map b.transactions.inputs by using source_tx.outputs, but I couldn't find how.

I came across this other answer, but I don't really understand how it applies to my scenario. Maybe it does, but I am very new to Couchbase, so I am very much lost: How to map array values in one document to another and display in result

Answer 1

Basically you want inline some other document into current document using condition.

Instead of JOINs+ GROUPS use subquery expressions + correlated subqueries. (b.*, "abc" AS transactions, selects all fields of b and adds transactions (if already exist overwrite else adds)

CREATE INDEX ix1 ON blocks (ALL ARRAY FOR ot.id FOR ot IN transactions END);

SELECT b.*,
       (SELECT t.*,
               (SELECT i.*,
                       (SELECT RAW ot
                        FROM blocks AS o
                        UNNEST o.transactions AS ot
                        UNNEST ot.outputs AS oto
                        WHERE i.tx_id = ot.id AND i.`index` = UNNEST_POS(oto))[0] AS output
                FROM t.`inputs` AS i) AS inputs
        FROM b.transactions AS t) AS transactions

FROM blocks AS b
WHERE ANY tx IN b.transactions SATISFIES tx.`inputs` IS NOT NULL END ;

OR

SELECT b.*,
       (SELECT t.*,
               (SELECT i.*,
                       (SELECT RAW ot.outputs[i.`index`] 
                        FROM blocks AS o
                        UNNEST o.transactions AS ot
                        WHERE i.tx_id = ot.id 
                        LIMIT 1)[0] AS output
                FROM t.`inputs` AS i) AS inputs
        FROM b.transactions AS t) AS transactions

FROM blocks AS b
WHERE ANY tx IN b.transactions SATISFIES tx.`inputs` IS NOT NULL END ;