How to create a table address and query it by states?

How can I create a table with following schema:

CREATE TABLE Customer
(
    id INT PRIMARY KEY,
    Name VARCHAR(128) NOT NULL,
    Email VARCHAR(128) NOT NULL,
    customer_id VARCHAR(16) NOT NULL,
    address NVARCHAR(max),
    created_time DateTimeOffset NOT NULL default SYSDATETIMEOFFSET()
)

CREATE UNIQUE INDEX idx_customer_email
ON Customer(customer_id, Email)

I wrote the above query for creating the table.

But I’m not able to link this table to another table for the query shown below and fetch all state names and total of account balance for the users belonging to those states.

CREATE TABLE Account
(
    id INT PRIMARY KEY,
    account_id VARCHAR(128) NOT NULL UNIQUE,
    account_no VARCHAR(16) NOT NULL,
    account_balance DECIMAL(16,2),
    user_id INT NOT NULL,
    created_date DateTimeOffset NOT NULL default SYSDATETIMEOFFSET(),
    FOREIGN KEY (user_id) REFERENCES Customer(id)
            ON DELETE CASCADE ON UPDATE CASCADE
)

CREATE INDEX idx_user_account
ON Account(user_id, account_balance)

Answer

You should be able to use something like this to query out records:

select
  a.*,
  JSON_VALUE(address, '$.state') as state,
  b.account_id
from customer a
inner join account b on a.id = b.user_id
where JSON_VALUE(a.address, '$.state') = 'maharashtra';

Let’s take an example.

insert into customer (id, name, email, customer_id, address) values
(
  1,
  'john parker',
  'a@a',
  'abc',
  '{"house_number":"10", "building_number": "manyata", "street": "vivekanand street", "city": "nagpur", "state": "maharashtra", "country": "india", "zipcode": "440001"}'
),
(
  2,
  'john parker',
  'a@b',
  'abc',
  '{"house_number":"10", "building_number": "manyata", "street": "vivekanand street", "city": "nagpur", "state": "gujarat", "country": "india", "zipcode": "392020"}'
);

These are two records – one for maharashtra and one for gujarat.

In your account able, you have 2 records as well like so:

insert into Account (id, account_id, account_no, user_id) values
(1, 'test', 'test', 1),
(2, 'test2', 'test2', 2);

When you run the query, you will get results for just maharastra.

See example here: http://sqlfiddle.com/#!18/c56741/1