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

Question

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

While creating a dbfiddle can be helpful as additional information, please also update your question with the SQL query, sample data and the result you want to achieve — NickW, Mar 12 '23 at 11:49
Does this answer your question? [Left Join With Where Clause](https://stackoverflow.com/questions/4752455/left-join-with-where-clause) — philipxy, Mar 12 '23 at 23:41
Please before considering posting: Pin down code issues via [mre]. Read manuals/references & google error messages & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort is expected of Stack Overflow users?](https://meta.stackoverflow.com/q/261592/3404097) [ask] [Help] Reflect research in posts. — philipxy, Mar 12 '23 at 23:43
Debug questions require a [mre]--cut & paste & runnable code including initialization; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. For SQL include DDL & tabular initialization code. For debug that includes the least code you can give that is code that you show is OK extended by code that you show is not OK. [ask] [Help] When you get a result you don't expect, pause your overall goal, chop to the 1st subexpression with unexpected result & say what you expected & why, justified by documentation. (Debugging fundamental.) — philipxy, Mar 12 '23 at 23:43

score 1 · Answer 1 · answered Mar 12 '23 at 12:47

1

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

answered Mar 12 '23 at 12:47

Serg

22,285
5
21
48

This returns 5 rows. I have found the issue, it is because the JSON array is strings and not integers but thank you for spending some time to help – Robert Mar 12 '23 at 12:54
Sorry I just tried your code and it works, even though I made my work I believe yours works just as well so I rewarded you with the answer – Robert Mar 12 '23 at 15:09

score 1 · Accepted Answer · answered Mar 12 '23 at 12:55

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

score 0 · Answer 3 · answered Mar 12 '23 at 12:55

0

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;

answered Mar 12 '23 at 12:55

Robert

907
4
13
32

This is not working for me, I have made a working solution did you checked it ? – SelVazi Mar 12 '23 at 13:03

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

3 Answers3