__pocketbook-db-request.sql

SELECT
HighlightID as ID,
COALESCE(Title, '') AS BookName,
COALESCE(Authors, '') AS Authors,
json_extract(Highlight, '$.text') AS Highlight,
BookID,
TimeEdit
FROM
Books
INNER JOIN (
SELECT
OID AS BookID,
Highlight,
HighlightID,
TimeEdit
FROM
Items
INNER JOIN (
SELECT
ParentID,
Highlight,
HighlightID,
TimeEdit
FROM
Items
INNER JOIN (
SELECT
OID as HighlightID,
ItemID,
Val AS Highlight,
TimeEdt as TimeEdit
FROM
Tags
WHERE
TagID = 104
AND Val <> '{\"text\":\"Bookmark\"}'
) AS Highlights ON Highlights.ItemID = OID
) AS Highlights ON Highlights.ParentID = OID
) AS Highlights ON BookID = OID;