We've got a query to grab running processes and some related info. Now we want to add in file descriptors and open files. This is what we've got so far:

SELECT t.iso_8601 AS _time,
       p.name AS process,
       p.pid AS process_id,
       p.cmdline,
       p.cwd,
       p.on_disk,
       p.resident_size AS mem_used,
       p.parent,
       g.groupname,
       g.gid AS group_id,
       u.username AS USER,
       u.uid AS user_id,
       eu.username AS effective_username,
       eg.groupname AS effective_groupname,
       p.path,
       h.md5,
       h.sha1,
       h.sha256,

  (SELECT json_group_array(json_object('fd',pof.fd, 'path',pof.path))
   FROM process_open_files AS pof
   WHERE pof.pid=p.pid
   GROUP BY pof.pid) AS openfiles
FROM processes AS p
LEFT JOIN users AS u ON p.uid=u.uid
LEFT JOIN users AS eu ON p.euid=eu.uid
LEFT JOIN groups AS g ON p.gid=g.gid
LEFT JOIN groups AS eg ON p.gid=eg.gid
LEFT JOIN hash AS h ON p.path=h.path
LEFT JOIN time AS t
WHERE parent IS NOT 2
  AND (process NOTNULL
       OR parent NOTNULL) LIMIT 1;

We export our data as json, so we want it in json format. We're collecting the open files with the

json_group_array

function so that we can nest the list of open files inside of the main process event. We're doing something wrong, but we're not sure how to do what we want, or if it's even possible. Ideally, the resulting json would look something like this:

{
  "_time": "2017-05-26T22:56:53Z",
  "cmdline": "/sbin/init splash",
  "cwd": "/",
  "effective_groupname": "root",
  "effective_username": "root",
  "group_id": "0",
  "groupname": "root",
  "md5": "7ead9434647f2990e5f7b7b10ebc0ff9",
  "mem_used": "3632000",
  "on_disk": "1",
  "openfiles": [
    {
      "fd": 0,
      "path": "/dev/null"
    },
    {
      "fd": 1,
      "path": "/dev/null"
    },
    {
      "fd": 10,
      "path": "/proc/1/mountinfo"
    },
    {
      "fd": 100,
      "path": "/dev/rfkill"
    },
    {
      "fd": 12,
      "path": "/proc/swaps"
    },
    {
      "fd": 2,
      "path": "/dev/null"
    },
    {
      "fd": 20,
      "path": "/dev/autofs"
    },
    {
      "fd": 3,
      "path": "/dev/kmsg"
    },
    {
      "fd": 6,
      "path": "/sys/fs/cgroup/systemd"
    },
    {
      "fd": 85,
      "path": "/run/systemd/initctl/fifo"
    }
  ],
  "parent": "0",
  "path": "/lib/systemd/systemd",
  "process": "systemd",
  "process_id": "1",
  "sha1": "4e7f920bc7b4a84726fd27b9b5ae87b78f656274",
  "sha256": "5cb0844dc3b8d7b98cf47a1fc9b2ce44d6f51adea2562ff6406b733d0ec90136",
  "user": "root",
  "user_id": "0"
}

Any suggestions?