Query which group object inside array inside object

SQL++
,
1

Hello, assume i have this document:

[
  {
    "code": "007",
    "elements": [
      {
        "a": "status",
        "b": 864,
        "c": "good",
		"d": "election",
        "e": "google",
        "f": "Trump[1].Status"
      },
      {
        "a": "function",
        "b": 876,
        "c": "president",
		"d": "election",
        "e": "twitter",
        "f": "Trump[1].mandat[0]"
      },
      {
        "a": "link",
        "b": 246,
        "c": "white-house",
		"d": "election",
        "e": "wordpress",
        "f": "Trump[1].link"
      },
      {
        "a": "status",
        "b": 963,
        "c": "bad",
		"d": "election",
        "e": "twitter",
        "f": "Biden[1].Status"
      },
      {
        "a": "function",
        "b": 753,
        "c": "politician",
		"d": "election",
        "e": "snapchat",
        "f": "Biden[1].mandat[0]"
      },
      {
        "a": "link",
        "b": 159,
        "c": "black-house",
		"d": "election",
        "e": "tiktok",
        "f": "Biden[1].link"
      },
      {
        "a": "status",
        "b": 456,
        "c": "gentle",
		"d": "election",
        "e": "facebook",
        "f": "Trump[2].Status"
      },
      {
        "a": "function",
        "b": 852,
        "c": "boss",
		"d": "election",
        "e": "twitter",
        "f": "Trump[2].mandat[0]"
      },
      {
        "a": "function",
        "b": 888,
        "c": "father",
		"d": "election",
        "e": "facebook",
        "f": "Trump[2].mandat[1]"
      },
      {
        "a": "link",
        "b": 579,
        "c": "trump-tower",
		"d": "election",
        "e": "twitter",
        "f": "Trump[2].link"
      }
	]
 }
}

i am despearatly searching for a query which will give me this:

[
  {
    "code": "007",
    "Trump": ["$1":{
        "c1": "good",//from status
        "c2": ["president"],//from mandat
        "c3": "white-house"//from link
      },
      "$2":{
        "c1": "gentle",//from status
        "c2": ["boss","father"],//from mandat
        "c3": "trump-tower"//from link
      }
	  ],
    "Biden": ["$1":{
        "c1": "bad",//from status
        "c2": ["politician"],//from mandat
        "c3": "black-house"//from link
      }
	  ]
 }
]

Any one can help me? I am stucked there!
Noticed: In the elements array, there are other objects with the “d” field different from “election”, i don’t care about them, i am just concern by d=“election”

2

Hi @cyrille,

INSERT INTO default VALUES ("f01",{ "code": "007", "elements": [ { "a": "status", "b": 864, "c": "good", "d": "election", "e": "google", "f": "Trump[1].Status" }, { "a": "function", "b": 876, "c": "president", "d": "election", "e": "twitter", "f": "Trump[1].mandat[0]" }, { "a": "link", "b": 246, "c": "white-house", "d": "election", "e": "wordpress", "f": "Trump[1].link" }, { "a": "status", "b": 963, "c": "bad", "d": "election", "e": "twitter", "f": "Biden[1].Status" }, { "a": "function", "b": 753, "c": "politician", "d": "election", "e": "snapchat", "f": "Biden[1].mandat[0]" }, { "a": "link", "b": 159, "c": "black-house", "d": "election", "e": "tiktok", "f": "Biden[1].link" }, { "a": "status", "b": 456, "c": "gentle", "d": "election", "e": "facebook", "f": "Trump[2].Status" }, { "a": "function", "b": 852, "c": "boss", "d": "election", "e": "twitter", "f": "Trump[2].mandat[0]" }, { "a": "function", "b": 888, "c": "father", "d": "election", "e": "facebook", "f": "Trump[2].mandat[1]" }, { "a": "link", "b": 579, "c": "trump-tower", "d": "election", "e": "twitter", "f": "Trump[2].link" }]} );

SELECT d2.code, OBJECT v.g1:v.val FOR v IN av END.*
FROM ( SELECT d1.code, g1, ARRAY_AGG({d1.g2:{c1,c2,c3}}) AS val
       FROM (SELECT d.code, g1, g2,  ARRAY_AGG(e) AS elements
             FROM default AS d
             UNNEST d.elements AS e
             LET g1 = SUBSTR(e.f,0,POSITION(e.f,"[")),
                 g2 = "$"|| SUBSTR(e.f,POSITION(e.f,"[")+1, POSITION(e.f,"]")-(POSITION(e.f,"[")+1))
             WHERE d.code = "007" AND e.d = "election"
             GROUP BY d.code, g1, g2) AS d1
       LET c1 = FIRST v.c FOR v IN d1.elements WHEN v.a = "status" END,
           c2 = ARRAY v.c FOR v IN d1.elements WHEN v.a = "function" END,
           c3 = FIRST v.c FOR v IN d1.elements WHEN v.a = "link" END
       GROUP BY d1.code, g1 ) AS d2
GROUP BY d2.code
LETTING av = ARRAY_AGG({d2.g1, d2.val});
3

@vsr1 KUDOS , you deserve a promotion!