Call rows LEFT JOIN if catid IS IN table2 JSON column and WHERE uid is in table2 JSON column

I am trying to call categories but only if a document is assigned to those categories.

The document has the categories stored as a JSON type. In the WHERE it is only bringing back the rows that have document uids to the user id who is looking. In this question I have set this as 1 to simply. This is also a JSON type.

It fails on the LEFT JOIN section so I cannot test if the WHERE statement is working.

CREATE TABLE IF NOT EXISTS `doccats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `pid` int(11) DEFAULT NULL,
  `groupnoreason` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;

INSERT INTO `doccats` (`id`, `name`, `pid`, `groupnoreason`) VALUES
(1, 'Test 1', NULL, 1),
(2, 'Test 2', NULL, 1),
(3, 'Sub Test', 1, 1),
(4, 'Inner Sub', 3, 1),
(5, 'Test 3', NULL, 1),
(6, 'Test 4', NULL, 1),
(9, 'Sub Test 2', 2, 1),
(10, 'Inner Sub 2', 2, 1),
(11, 'Sub Test 3', 5, 1),
(12, 'Inner Sub 2', 5, 1),
(13, 'Sub Test 4', 6, 1),
(14, 'Inner Sub 4', 6, 1),
(15, 'Sub Sub 1', 13, 1),
(16, 'Sub Sub 2', 13, 1),
(17, 'asdasdad', 3, 1),
(18, 'fffff', 1, 1),
(19, 'Testing INNNNNNNER', 15, 1),
(20, 'ioausdhioauhduia', 19, 1),
(21, 'dsfsdfdsfsdfsdf', 20, 1),
(22, 'fghfghfghfgh', 21, 1),
(23, 'sdfsdf', 22, 1),
(24, 'fghfghf', 23, 1),
(25, 'ghjghjhgj', 24, 1),
(26, 'hjkhjkhjk', 25, 1),
(27, '567567576', 25, 1),
(28, '678967fghfghfgh', 25, 1),
(29, '345345345453345', 24, 1);

CREATE TABLE IF NOT EXISTS `documents` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `file` varchar(255) NOT NULL,
  `uids` json NOT NULL,
  `duids` json DEFAULT NULL,
  `suids` json DEFAULT NULL,
  `cats` json NOT NULL,
  `uploaded` datetime NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 COMMENT='suids';

INSERT INTO `documents` (`id`, `name`, `description`, `file`, `uids`, `duids`, `suids`, `cats`, `uploaded`, `status`) VALUES
(1, 'Test Document 1', 'osidfhjosidjf soijfsiodjfsoidjfoisdjfosidjf soidjfoisdjfoisdjfoisjfij oj oij ojoijjoisdjfiosdjf sdf sdfsdfoijoi oijsdf', 'G04ClNGZIJD4n4I0TizW8kYPdGZHkVPT.pdf', '[\"1\", \"2\", \"3\"]', NULL, NULL, '[\"1\", \"2\", \"3\"]', '2023-03-11 13:46:03', 1),
(2, 'Test another', 'fdsfsdfsdfs dsfsdfsdf sdfsdfdfdf', 'lowM5s3kXstpU4XJ3mstlGpDxcVckaS5.pdf', '[\"2\", \"7\", \"9\", \"10\", \"88\"]', NULL, NULL, '[\"4\", \"10\", \"23\", \"26\"]', '2023-03-11 14:31:29', 1);

SELECT 
dc.id, dc.pid, dc.name 
FROM doccats dc 
LEFT JOIN documents d ON JSON_CONTAINS(d.cats, CAST(dc.id AS JSON)) 
WHERE JSON_CONTAINS(d.uids, CAST(1 AS JSON)) 
ORDER BY dc.name ASC

The expected outcome of this query is to return 3 rows which would be:

dc.id, dc.pid, dc.name 
(1, NULL, 'Test 1'),
(2, NULL, 'Test 2'),
(3, 1, 'Sub Test')

www.db-fiddle.com

You should compare JSON array to an array not a scalar. For example JSON_CONTAINS(cats,'["1"]').

Not sure why LEFT JOIN. You can use inner JOIN and DISTINCT. Alternatively, finding categories with at least 1 document assigned using EXISTS

SELECT 
dc.id, dc.pid, dc.name 
FROM doccats dc 
WHERE EXISTS (
  SELECT 1 
  FROM documents d 
  WHERE JSON_CONTAINS(d.cats, CAST(concat('["',dc.id,'"]') AS JSON))
  )
ORDER BY dc.name ASC

INNER JOIN is ok for your case

You need to search an array of strings as follows:

JSON_CONTAINS(d.uids, '"1"', '$')

SQL query :

SELECT 
dc.id as catID, dc.name as catName, dc.pid, d.id as docID
FROM doccats dc 
INNER JOIN documents d ON JSON_CONTAINS(cats, CONCAT("\"",dc.id, "\""))
WHERE JSON_CONTAINS(d.uids, '"1"') 
ORDER BY dc.id

Demo here

I found the issue, it is because the stored json array is:

["1","2","3"] 

and not

[1,2,3]

The SQL Query that has made it work is:

SELECT dc.id, dc.pid, dc.name 
FROM doccats dc 
LEFT JOIN documents d ON JSON_CONTAINS(d.cats, CAST(dc.id AS JSON)) 
WHERE JSON_CONTAINS(d.uids, CAST('"1"' AS JSON)) 
ORDER BY dc.name ASC;