N1QL combine objects in an array based on specific field values being equal

SQL++
1

This must be something simple that I am missing. I have a complex query that produces a simple array of objects, each with three fields, “modelFcstValidEpoch, modelStationName, modelCeiling” or “obsFcstValidEpoch, obsStationName, obsCeiling”. A sample of the data is below. I want to combine these objects based on “obsFcstValidEpoch = modelFcstValidEpoch” AND “obsStationName = modelStationName” such that I would have an array of
[ { name: someName, fcstValidTime:sometime, obsCeiling:something, modelCeiling:something }, { name: someName, fcstValidTime:somethime, obsCeiling:something, modelCeiling:something }, .... ]
I seem to be having a difficult time figuring this out. Any help would be greatly appreciated.
Here is the data… I didn’t provide the complex query since it is really just part that I don’t seem to understand.
Notice that
{ "obsCeiling": 6000, "obsFcstValidEpoch": 1614592800, "obsStationName": "KGEY" }
should match with
{ "modelCeiling": 6162, "modelFcstValidEpoch": 1614592800, "modelStationName": "KGEY" }
under my criteria.
[ { "obsCeiling": 6000, "obsFcstValidEpoch": 1614589200, "obsStationName": "KBYG" }, { "obsCeiling": 6000, "obsFcstValidEpoch": 1614589200, "obsStationName": "KGEY" }, { "obsCeiling": 6000, "obsFcstValidEpoch": 1614589200, "obsStationName": "KSHR" }, { "obsCeiling": 6000, "obsFcstValidEpoch": 1614592800, "obsStationName": "KBYG" }, { "obsCeiling": 6000, "obsFcstValidEpoch": 1614592800, "obsStationName": "KGEY" }, { "obsCeiling": 6000, "obsFcstValidEpoch": 1614592800, "obsStationName": "KSHR" }, { "obsCeiling": 6000, "obsFcstValidEpoch": 1614596400, "obsStationName": "KBYG" }, { "obsCeiling": 6000, "obsFcstValidEpoch": 1614596400, "obsStationName": "KGEY" }, { "obsCeiling": 6000, "obsFcstValidEpoch": 1614596400, "obsStationName": "KSHR" }, { "obsCeiling": 6000, "obsFcstValidEpoch": 1614600000, "obsStationName": "KBYG" }, { "obsCeiling": 6000, "obsFcstValidEpoch": 1614600000, "obsStationName": "KGEY" }, { "obsCeiling": 6000, "obsFcstValidEpoch": 1614600000, "obsStationName": "KSHR" }, { "obsCeiling": 6000, "obsFcstValidEpoch": 1614603600, "obsStationName": "KBYG" }, { "obsCeiling": 6000, "obsFcstValidEpoch": 1614603600, "obsStationName": "KGEY" }, { "obsCeiling": 6000, "obsFcstValidEpoch": 1614603600, "obsStationName": "KSHR" }, { "modelCeiling": 6084, "modelFcstValidEpoch": 1614592800, "modelStationName": "KBYG" }, { "modelCeiling": 6162, "modelFcstValidEpoch": 1614592800, "modelStationName": "KGEY" }, { "modelCeiling": 6175, "modelFcstValidEpoch": 1614592800, "modelStationName": "KSHR" }, { "modelCeiling": 6084, "modelFcstValidEpoch": 1614596400, "modelStationName": "KBYG" }, { "modelCeiling": 6162, "modelFcstValidEpoch": 1614596400, "modelStationName": "KGEY" }, { "modelCeiling": 6175, "modelFcstValidEpoch": 1614596400, "modelStationName": "KSHR" }, { "modelCeiling": 6084, "modelFcstValidEpoch": 1614600000, "modelStationName": "KBYG" }, { "modelCeiling": 6162, "modelFcstValidEpoch": 1614600000, "modelStationName": "KGEY" }, { "modelCeiling": 6176, "modelFcstValidEpoch": 1614600000, "modelStationName": "KSHR" }, { "modelCeiling": 6084, "modelFcstValidEpoch": 1614603600, "modelStationName": "KBYG" }, { "modelCeiling": 6162, "modelFcstValidEpoch": 1614603600, "modelStationName": "KGEY" }, { "modelCeiling": 6175, "modelFcstValidEpoch": 1614603600, "modelStationName": "KSHR" }, { "modelCeiling": 6084, "modelFcstValidEpoch": 1614607200, "modelStationName": "KBYG" }, { "modelCeiling": 6162, "modelFcstValidEpoch": 1614607200, "modelStationName": "KGEY" }, { "modelCeiling": 6176, "modelFcstValidEpoch": 1614607200, "modelStationName": "KSHR" } ]
Thanks in advance.

2

Sorry for misquoting.

{ "obsCeiling": 6000, "obsFcstValidEpoch": 1614592800, "obsStationName": "KGEY" }

should match

{ "modelCeiling": 6162, "modelFcstValidEpoch": 1614592800, "modelStationName": "KGEY" }

and

[
  {
    "obsCeiling": 6000,
    "obsFcstValidEpoch": 1614589200,
    "obsStationName": "KBYG"
  },
  {
    "obsCeiling": 6000,
    "obsFcstValidEpoch": 1614589200,
    "obsStationName": "KGEY"
  },
  {
    "obsCeiling": 6000,
    "obsFcstValidEpoch": 1614589200,
    "obsStationName": "KSHR"
  },
  {
    "obsCeiling": 6000,
    "obsFcstValidEpoch": 1614592800,
    "obsStationName": "KBYG"
  },
  {
    "obsCeiling": 6000,
    "obsFcstValidEpoch": 1614592800,
    "obsStationName": "KGEY"
  },
  {
    "obsCeiling": 6000,
    "obsFcstValidEpoch": 1614592800,
    "obsStationName": "KSHR"
  },
  {
    "obsCeiling": 6000,
    "obsFcstValidEpoch": 1614596400,
    "obsStationName": "KBYG"
  },
  {
    "obsCeiling": 6000,
    "obsFcstValidEpoch": 1614596400,
    "obsStationName": "KGEY"
  },
  {
    "obsCeiling": 6000,
    "obsFcstValidEpoch": 1614596400,
    "obsStationName": "KSHR"
  },
  {
    "obsCeiling": 6000,
    "obsFcstValidEpoch": 1614600000,
    "obsStationName": "KBYG"
  },
  {
    "obsCeiling": 6000,
    "obsFcstValidEpoch": 1614600000,
    "obsStationName": "KGEY"
  },
  {
    "obsCeiling": 6000,
    "obsFcstValidEpoch": 1614600000,
    "obsStationName": "KSHR"
  },
  {
    "obsCeiling": 6000,
    "obsFcstValidEpoch": 1614603600,
    "obsStationName": "KBYG"
  },
  {
    "obsCeiling": 6000,
    "obsFcstValidEpoch": 1614603600,
    "obsStationName": "KGEY"
  },
  {
    "obsCeiling": 6000,
    "obsFcstValidEpoch": 1614603600,
    "obsStationName": "KSHR"
  },
  {
    "modelCeiling": 6084,
    "modelFcstValidEpoch": 1614592800,
    "modelStationName": "KBYG"
  },
  {
    "modelCeiling": 6162,
    "modelFcstValidEpoch": 1614592800,
    "modelStationName": "KGEY"
  },
  {
    "modelCeiling": 6175,
    "modelFcstValidEpoch": 1614592800,
    "modelStationName": "KSHR"
  },
  {
    "modelCeiling": 6084,
    "modelFcstValidEpoch": 1614596400,
    "modelStationName": "KBYG"
  },
  {
    "modelCeiling": 6162,
    "modelFcstValidEpoch": 1614596400,
    "modelStationName": "KGEY"
  },
  {
    "modelCeiling": 6175,
    "modelFcstValidEpoch": 1614596400,
    "modelStationName": "KSHR"
  },
  {
    "modelCeiling": 6084,
    "modelFcstValidEpoch": 1614600000,
    "modelStationName": "KBYG"
  },
  {
    "modelCeiling": 6162,
    "modelFcstValidEpoch": 1614600000,
    "modelStationName": "KGEY"
  },
  {
    "modelCeiling": 6176,
    "modelFcstValidEpoch": 1614600000,
    "modelStationName": "KSHR"
  },
  {
    "modelCeiling": 6084,
    "modelFcstValidEpoch": 1614603600,
    "modelStationName": "KBYG"
  },
  {
    "modelCeiling": 6162,
    "modelFcstValidEpoch": 1614603600,
    "modelStationName": "KGEY"
  },
  {
    "modelCeiling": 6175,
    "modelFcstValidEpoch": 1614603600,
    "modelStationName": "KSHR"
  },
  {
    "modelCeiling": 6084,
    "modelFcstValidEpoch": 1614607200,
    "modelStationName": "KBYG"
  },
  {
    "modelCeiling": 6162,
    "modelFcstValidEpoch": 1614607200,
    "modelStationName": "KGEY"
  },
  {
    "modelCeiling": 6176,
    "modelFcstValidEpoch": 1614607200,
    "modelStationName": "KSHR"
  }
]
3 
WITH  data AS (your data)
SELECT d.obsCeiling, d1.modelCeiling, d.obsFcstValidEpoch AS fcstValidTime, d.obsStationName AS  name
FROM data AS d 
UNNEST data AS d1
WHERE d.obsFcstValidEpoch = d1.modelFcstValidEpoch 
      AND  d.obsStationName = d1.modelStationName;

OR

ARRAY_FLATTEN((ARRAY (ARRAY  {v1.obsCeiling, v2.modelCeiling, "fcstValidTime":v1.obsFcstValidEpoch, "name":v1.obsStationName}
                      FOR v2 IN data[pos+1:]  
                      WHEN  v1.obsFcstValidEpoch = v2.modelFcstValidEpoch 
                            AND v1.obsStationName = v2.modelStationName 
                      END) 
               FOR pos:v1 IN data 
               END),1)
4

Thank you very much!