sqlite_play.json

[
    {
        "id": "1",
        "title": "Post A",
        "category": {
            "id": "1",
            "name": "Category A"
        },
        "comments": [
            {
                "id": "1",
                "content": "Comment A"
            },
            {
                "id": "2",
                "content": "Comment B"
            }
        ],
        "tags": [
            {
                "id": "1",
                "name": "Tag A"
            }
        ]
    },
    {
        "id": "2",
        "title": "Post B",
        "category": {
            "id": "1",
            "name": "Category A"
        },
        "comments": [
            {
                "id": "3",
                "content": "Comment C"
            },
            {
                "id": "4",
                "content": "Comment D"
            }
        ],
        "tags": [
            {
                "id": "1",
                "name": "Tag A"
            }
        ]
    },
    {
        "id": "3",
        "title": "Post C",
        "category": {
            "id": "2",
            "name": "Category B"
        },
        "comments": [
            {
                "id": "5",
                "content": "Comment E"
            },
            {
                "id": "6",
                "content": "Comment F"
            }
        ],
        "tags": [
            {
                "id": "2",
                "name": "Tag B"
            }
        ]
    },
    {
        "id": "4",
        "title": "Post D",
        "category": {
            "id": "2",
            "name": "Category B"
        },
        "comments": [
            {
                "id": "7",
                "content": "Comment G"
            },
            {
                "id": "8",
                "content": "Comment H"
            }
        ],
        "tags": [
            {
                "id": "2",
                "name": "Tag B"
            }
        ]
    }
]

sqlite_play.php

<?php

$pdo = new PDO('sqlite::memory:');

function query ($sql, $args = []) {
  global $pdo;
  $stmt = $pdo->prepare($sql);
  $stmt->execute($args);
  return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

query('CREATE TABLE Category (id PRIMARY KEY, name)');
query('CREATE TABLE Tag (id PRIMARY KEY, name)');
query('CREATE TABLE Post (id PRIMARY KEY, title, category)');
query('CREATE TABLE Comment (id PRIMARY KEY, content, post)');
query('CREATE TABLE PostTag (post, tag, PRIMARY KEY(post, tag))');

$m = 2;

for ($i = 0; $i < $m; $i++) {
  $category = [
    $i + 1,
    'Category ' . chr(65 + $i)
  ];
  $tag = [
    $i + 1,
    'Tag ' . chr(65 + $i)
  ];
  query('INSERT INTO Category VALUES (?, ?)', $category);
  query('INSERT INTO Tag VALUES (?, ?)', $tag);
  for ($j = 0; $j < $m; $j++) {
    $post = [
      $i * $m + $j + 1,
      'Post ' . chr(65 + ($i * $m + $j)),
      $i + 1
    ];
    query('INSERT INTO Post VALUES (?, ?, ?)', $post);
    for ($k = 0; $k < $m; $k++) {
      $comment = [
        $i * $m * $m + $j * $m + $k + 1,
        'Comment ' . chr(65 + ($i * $m * $m + $j * $m + $k)),
        $i * $m + $j + 1
      ];
      query('INSERT INTO Comment VALUES (?, ?, ?)', $comment);
    }
    $postTag = [
      $i * $m + $j + 1,
      $i + 1
    ];
    query('INSERT INTO PostTag VALUES (?, ?)', $postTag);
  }
}

$result = query("
  SELECT json_object(
    'id', id,
    'title', title,
    'category', (
      SELECT json_object('id', id, 'name', name)
      FROM Category
      WHERE id = category
    ),
    'comments', (
      SELECT json_group_array(json_object('id', id, 'content', content))
      FROM Comment
      WHERE post = Post.id
    ),
    'tags', (
      SELECT json_group_array(json_object('id', id, 'name', name))
      FROM Tag
        JOIN PostTag ON Tag.id = PostTag.tag
      WHERE post = Post.id
    )
  ) AS json FROM Post
");

echo json_encode(array_map(fn ($x) => json_decode(current($x)), $result), JSON_UNESCAPED_SLASHES | JSON_PRETTY_PRINT);