Can JSON type be used for join queries with other tables?

Chinese Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: json类型可以和和其他表进行关联查询吗

| username: TiDBer_7Q5CQdQd

Can the JSON type be associated with other tables for queries?
image
image
How to write SQL?

2
| username: Billmay表妹 | Original post link

TiDB supports querying on JSON type columns, but JSON type columns cannot be directly joined with other tables. If you need to perform join queries on JSON type columns, you can consider using TiDB’s Generated Column feature.

A Generated Column is a virtual column whose value is computed from other columns. You can use a Generated Column to extract certain fields from a JSON type column and then use these fields as the values of the Generated Column. This way, you can perform join queries on the Generated Column.

Here is an example. Suppose you have a table named user that contains a JSON type column info, which includes the user’s name and age information:

CREATE TABLE user (
  id INT PRIMARY KEY,
  info JSON
);

INSERT INTO user VALUES (1, '{"name": "Alice", "age": 20}');
INSERT INTO user VALUES (2, '{"name": "Bob", "age": 30}');

Now, suppose you have another table named order that contains a user_id column representing the user ID to which the order belongs. If you want to query the name and age of the user to whom the order belongs, you can use Generated Columns to extract the name and age information from the info column in the user table:

ALTER TABLE user ADD COLUMN name VARCHAR(255) GENERATED ALWAYS AS (info->"$.name") VIRTUAL;
ALTER TABLE user ADD COLUMN age INT GENERATED ALWAYS AS (info->"$.age") VIRTUAL;

SELECT order.id, user.name, user.age
FROM order
JOIN user ON order.user_id = user.id;

In the above example, we use the ALTER TABLE statement to create two Generated Columns that extract the name and age information from the info column. Then, we can perform join queries on the Generated Columns in the user table to get the name and age of the user to whom the order belongs.

3
| username: hey-hoho | Original post link

In what scenarios would you need such associations? If you really need to do this, I think you should adjust the data model.
TiDB doesn’t seem to handle this well, but PostgreSQL seems to be capable.

4
| username: redgame | Original post link

No, it can’t.

5
| username: TiDBer_7Q5CQdQd | Original post link

How to write a subquery, how should it be written?

6
| username: TiDBer_7Q5CQdQd | Original post link

Can I use a subquery? If so, how should it be written?

7
| username: TiDBer_7Q5CQdQd | Original post link

Is a subquery possible? If so, how should it be written?