Nest Multiple Sub Document in Parent Via Single Query

SQL++
, ,
1

I have three documents like below

key : parent::1

  {
    "id" : "1",
    "preview": {
      "sub-doc": {
        "id" : "1"
      },
      "child-doc": {
        "id" : "2"
      }
    }
  }

sub::1

  {
    "id" : "1",
    "picture": {
      "avatar" : "url"
    }
  }

child::2

  {
        "id": "2",
        "name": {
          "first": "First Name",
          "second": "Second Name"
        }
   }

I want to nest the subdoc into parent and expected output is like this

key : parent::parent-id

  {
        "id" : "1",
        "preview": {
          "sub-doc": {
            "id" : "1",
            "picture": {
              "avatar" : "url"
            }
          },
          "child-doc": {
            "id": "2",
            "name": {
              "first": "First Name",
              "second": "Second Name"
            }
          }
        }
      }

Since i know each of the key so i will use the key in parent for ANSI join

2 
SELECT p.*,  {"sub-doc":IFMISSINGORNULL(s,p.review.`sub-doc`), 
              "child-doc":IFMISSINGORNULL(ch,p.review.`child-doc`)
             } AS preview
FROM default AS p 
LET s = (SELECT RAW sd FROM default AS sd USE KEYS ["sub::" ||p.preview.`sub-doc`.id] )[0],
    c =  (SELECT RAW ch FROM default AS ch USE KEYS ["child::" ||p.preview.`child-doc`.id] )[0]
WHERE  p.id = "parent-id";

You can use ANSI JOIN also. Only important step will be Object Construction in the projection.

1 Like
3

Your solution works perfectly for Single preview object how ever i was practicing what happen if the preview field is and array like this ?

key : parent::1

  {
    "id" : "1",
    "preview": [
     {
      "sub-doc": {
        "id" : "1"
      },
      "child-doc": {
        "id" : "2"
      }
     }
   ]
  }
4 
SELECT d.*, ARRAY_AGG(CASE WHEN p.`sub-doc` IS NOT MISSING
                           THEN {"sub-doc":IFMISSINGORNULL(j, p.`sub-doc`)}
                           ELSE {"child-doc":IFMISSINGORNULL(j, p.`child-doc`)}
                      END) AS preview
FROM default AS d
UNNEST p.preview AS p
LEFT JOIN default AS j ON KEYS (CASE WHEN p.`sub-doc` IS NOT MISSING
                                THEN "sub::"||p.`sub-doc`.id
                                ELSE "child::"||p.`child-doc`.id
                           END)
WHERE d.id = "parent-id"
GROUP BY d