Update documents by using a subquery

SQL++
,
1

I have a LanguageEntity document like below:

select a.*, meta(a).id
from default as a
where a._class = "com.foo.bar.LanguageEntity"

[
...
  {
    "_class": "com.foo.bar.LanguageEntity",
    "appId": "a1",
    "code": "en",
    "createdAt": 1540364251719,
    "deleted": false,
    "id": "a1::en",
    "lastModifiedAt": 1540364251719,
    "name": "English"
  },
...
]

So each application may have multiple languages attached to them.

For optimization, I decided that it’s much better to put language codes into the ApplicationEntity which will eventually prevent further queries for relations:

  {
    "_class": "com.foo.bar.ApplicationEntity",
    "id": "application::a1",
    "languages": [
      "en",
      "de"
    ],
    "name": "a1",
    "userIds": [
      "foo@bar.com"
    ]
  }

I’m trying to write a migration script to achieve this. I thought, if I could make a list of appId’s vs. languages, I could use this subquery to update ApplicationEntity's which seems doable but I couldn’t really figure it out. Below is my query to create a list of appId’s with their languages:

select ("application::" || a.appId) as appId, array_agg(a.code) as lang
from default as a
where a._class = "com.foo.bar.LanguageEntity"
group by a.appId

[
  {
    "appId": "application::a2",
    "lang": [
      "tr"
    ]
  },
  {
    "appId": "application::a1",
    "lang": [
      "de",
      "en"
    ]
  },
  {
    "appId": "application::a3",
    "lang": [
      "en",
      "tr"
    ]
  },
  {
    "appId": "application::a4",
    "lang": [
      "en",
      "tr"
    ]
  },
  {
    "appId": "application::a5",
    "lang": [
      "en"
    ]
  }
]

Is there a way to update applications like below?

update default as a
set a.languages = b.lang
where a._class = "com.foo.bar.ApplicationEntity"
    and ANY lng IN (SUBQUERY) as b satisfies a.appId = b.appId
2

Looks like below query does the trick:

merge into default as a
using (
    select appId, array_agg(code) as lang
    from default
    where _class = "com.foo.bar.LanguageEntity"
    group by appId
) as b
on key "application::" || b.appId
when matched then update set a.languages = b.lang
returning meta(a).id;
1 Like