همکاری با JSON در PostgreSQL ، MySQL و SQL Server

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  profile JSONB
);

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  profile JSON
);

CREATE TABLE users (
  id INT IDENTITY PRIMARY KEY,
  profile NVARCHAR(MAX) -- must be valid JSON
);

-- Extract scalar field
SELECT profile->>'name' AS name FROM users;

-- Extract nested object
SELECT profile->'address'->>'city' AS city FROM users;

-- Extract with JSON_EXTRACT
SELECT JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name')) AS name FROM users;

-- Nested access
SELECT JSON_UNQUOTE(JSON_EXTRACT(profile, '$.address.city')) AS city FROM users;

-- Extract scalar field
SELECT JSON_VALUE(profile, '$.name') AS name FROM users;

-- Extract nested object
SELECT JSON_VALUE(profile, '$.address.city') AS city FROM users;

INSERT INTO users (profile)
VALUES ('{"name": "Ada", "skills": ["SQL", "Python"]}');

SELECT profile FROM users;

-- Get users with SQL skill
-- PostgreSQL
SELECT * FROM users WHERE profile->'skills' ? 'SQL';

-- MySQL
SELECT * FROM users WHERE JSON_CONTAINS(profile->'$.skills', '"SQL"');

-- SQL Server
SELECT * FROM users WHERE JSON_QUERY(profile, '$.skills') LIKE '%SQL%';

-- Index top-level key
CREATE INDEX idx_profile_name ON users ((profile->>'name'));

-- Full GIN index
CREATE INDEX idx_profile_json ON users USING GIN (profile);

-- Generated column (MySQL 5.7+)
ALTER TABLE users ADD name_gen VARCHAR(255) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.name'))) STORED;
CREATE INDEX idx_name_gen ON users(name_gen);

-- Create computed column
ALTER TABLE users ADD name AS JSON_VALUE(profile, '$.name');
CREATE INDEX idx_name ON users(name);

SELECT jsonb_build_object('id', id, 'profile', profile) FROM users;

SELECT id, profile FROM users FOR JSON PATH;

SELECT JSON_OBJECT('id', id, 'profile', profile) FROM users;