How to convert a string to JSON in Postgres?

Cast it: SELECT '{"id":1}'::jsonb . Use jsonb rather than json unless you specifically need the original text preserved — only jsonb supports indexing and the containment operators.

json or jsonb in Postgres?

json stores the exact text, including whitespace and duplicate keys, and re-parses on every read. jsonb stores a decomposed binary form: slightly slower to write, far faster to query, and the only one that can be GIN-indexed. Choose jsonb by default.

How do I convert JSON to a string in Postgres?

Cast back with ::text , or use jsonb_pretty(col) for indented output. Note that col->>'key' already returns text, while col->'key' returns JSON.

What does SQL Server offer?

SQL Server has no JSON data type — JSON lives in nvarchar . Use ISJSON() to validate, JSON_VALUE() for a scalar, JSON_QUERY() for an object or array, and OPENJSON() to expand it into rows.

How do I turn a JSON array into a comma-separated string?

In SQL Server, SELECT STRING_AGG(value, ',') FROM OPENJSON(@json) . In Postgres the equivalent is SELECT string_agg(value, ',') FROM jsonb_array_elements_text(col) .

Convert String to JSON in SQL — Postgres, MySQL, SQL Server

PostgreSQL

-- text to JSON: a plain cast
SELECT '{"id":1,"tags":["a","b"]}'::jsonb;

-- convert an existing text column
ALTER TABLE events ALTER COLUMN payload TYPE jsonb USING payload::jsonb;

-- reading values
SELECT payload->'user'    AS as_json,   -- returns jsonb
       payload->>'id'     AS as_text,   -- returns text
       payload#>>'{user,name}' AS nested
FROM events;

-- JSON back to string
SELECT payload::text, jsonb_pretty(payload) FROM events;

Postgres has the strongest JSON support of any relational database. The distinction that matters is -> versus ->>: one arrow returns JSON, two arrows return text. Mixing them up is the cause of most "operator does not exist" errors. Add a GIN index on ajsonb column and containment queries with @> become genuinely fast.

MySQL

SELECT CAST('{"id":1}' AS JSON);
SELECT JSON_VALID(payload) FROM events;
SELECT JSON_EXTRACT(payload, '$.user.name');   -- or payload->>'$.user.name'

MySQL 5.7+ has a native JSON type that validates on insert and normalizes key order. The ->> shorthand unquotes the result, saving a wrap inJSON_UNQUOTE.

SQL Server

-- no JSON type: store as nvarchar with a validity check
ALTER TABLE events ADD CONSTRAINT payload_is_json CHECK (ISJSON(payload) = 1);

SELECT JSON_VALUE(payload, '$.user.name') AS name,   -- scalar
       JSON_QUERY(payload, '$.items')     AS items   -- object or array
FROM events;

-- expand an array into rows
SELECT sku FROM OPENJSON(@json) WITH (sku nvarchar(50) '$.sku');

-- array to a comma-separated string
SELECT STRING_AGG(value, ',') FROM OPENJSON(@json);

Using JSON_VALUE on an object returns NULL rather than raising an error, which makes typos in the path hard to spot. If a lookup silently returns nothing, check whether you should be using JSON_QUERY instead.

Snowflake and Redshift

-- Snowflake
SELECT PARSE_JSON(payload):user:name::string FROM events;
SELECT TRY_PARSE_JSON(payload) FROM events;   -- NULL instead of an error

-- Redshift
SELECT JSON_PARSE(payload) FROM events;       -- to SUPER
SELECT json_extract_path_text(payload, 'user', 'name') FROM events;

Snowflake's VARIANT type and colon path syntax are the most concise of the group, andTRY_PARSE_JSON is invaluable when loading messy data — it yields NULLfor bad rows instead of failing the whole statement. Redshift's SUPER type plays the same role.

Should you store JSON in a relational database at all?

The honest answer is: for the parts of your data whose shape you genuinely do not control. A webhook payload, a third-party API response, a per-tenant settings blob — those are good JSON columns, because inventing a table for a schema that changes without warning creates more migrations than it prevents bugs.

Everything else belongs in ordinary columns. A field you filter on, sort by, join against or constrain with a foreign key should be a real column with a real type. JSON columns give up almost every guarantee the database exists to provide: no type checking on the values, no referential integrity, no NOT NULL on a nested field, and query plans that are far harder to reason about. The common compromise is to store the raw document and promote the handful of fields you actually query into generated or trigger-maintained columns, which Postgres, MySQL and SQL Server all support.

Validate on the way in

A jsonb or JSON column rejects malformed input automatically — the cast fails and the insert fails with it. SQL Server has no such type, so add anISJSON() check constraint or you will discover the bad rows months later, when a report crashes on them. On Snowflake and Redshift, TRY_PARSE_JSON and its equivalents let a bulk load survive individual bad records instead of aborting the batch.

Indexing is the whole performance story

Without an index, every query against a JSON column is a full scan that parses each document as it goes. Postgres GIN indexes on jsonb, MySQL indexes on generated columns extracted with JSON_EXTRACT, and SQL Server indexes on computedJSON_VALUE columns all turn that scan into a lookup. Build the index around the specific paths you query rather than the whole document — a general-purpose index on a large JSON column is mostly wasted space.

Whichever engine you are on, validating the payload before it reaches the database saves a round trip. Paste it into the panel above and you will get the exact line and column of any syntax error.

Convert a string to JSON in SQL.

JSON

Normalized JSON