title: MySQL join query with JSON field types
body: <p>I have three tables. In categories I have 18 records and in areas I have 8 records.</p>
  
  <p>In the client_profiles table the areas field may look like [<a href="https://i.stack.imgur.com/lue8R.png" rel="nofollow noreferrer">1</a>] or [2] or [1,3] etc</p>
  
  <p>categories will be something along the lines of [<a href="https://i.stack.imgur.com/lue8R.png" rel="nofollow noreferrer">1</a>] or [2] or [1,3] etc</p>
  
  <p>A client_profile must have at least one area and at least one category.</p>
  
  <p>Say I have 100 client_profiles for example. Each one belongs to an area and each one has at least one category.</p>
  
  <p>lets say I am doing area = 1, I am looking for all categories (regardless) and how many client_profiles are in each category. </p>
  
  <p>How do I achieve he following:</p>
  
  <p><a href="https://i.stack.imgur.com/lue8R.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/lue8R.png" alt="results example"></a></p>
  
  <p>etc.</p>
  
  <p>table examples below.</p>
  
  <pre><code>CREATE TABLE categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_description VARCHAR(255) NOT NULL UNIQUE,
  isActive boolean NOT NULL DEFAULT true
  );
  
  CREATE TABLE areas (
  id INT AUTO_INCREMENT PRIMARY KEY,
  area_description VARCHAR(255) NOT NULL UNIQUE,
  isActive boolean NOT NULL DEFAULT true
  );
  
  CREATE TABLE client_profiles (
  id INT AUTO_INCREMENT PRIMARY KEY,
  businessName VARCHAR(60) NOT NULL UNIQUE,
  first_name VARCHAR(60) NOT NULL,
  last_name VARCHAR(60) NOT NULL,
  mob_no VARCHAR(13) NOT NULL,
  email VARCHAR(30) NOT NULL,
  areas JSON NOT NULL,
  catarea JSON NOT NULL,
  );
  
  select id, areas 
  from client_profiles
  where JSON_CONTAINS(areas, '1',"$");
  
  INSERT INTO categories (category_description, isActive) values 
  ('Accomodation', true),
  ('Automative', true),
  ('Adult', true),
  ('Arts &amp; Crafts', true),
  ('Events', true),
  ('Financial', true),
  ('Garden', true),
  ('House &amp; Home', true),
  ('IT &amp; Technology', true),
  ('Kids', true),
  ('Legal', true),
  ('Medical', true),
  ('Pets', true),
  ('Pregnancy(Babies)', true),
  ('Property', true),
  ('Travel &amp; Tourism', true),
  ('Transport', true),
  ('Wellness &amp; Beauty', true);
  </code></pre>