如何用SQL查询序列化DOCINF和UDINF两张表

Hey there! To help you nail the right SQL query for serializing your DOCINF and UDINF tables, I need a couple more details about your setup—but let’s break down common scenarios and examples to get you moving in the right direction.

Before diving into exact queries, it’s critical to define:

• Table Structures: What fields exist in DOCINF and UDINF? What’s the relationship between them (e.g., a foreign key like doc_id linking UDINF records to a parent DOCINF entry)?
• Target Serialization Format: Are you looking to combine related rows into a comma-separated string? Nest data into a JSON/XML structure? Or something else entirely?

Let’s assume a common setup where:

• DOCINF is a main document table with fields like doc_id (primary key), doc_title, created_date
• UDINF is a related user table with fields like udin_id, doc_id (foreign key to DOCINF), user_name, user_permission

Example 1: Serialize Related User Data into a JSON Array (MySQL) #

If you want to package all users associated with a document into a JSON array:

SELECT
    d.doc_id,
    d.doc_title,
    d.created_date,
    JSON_ARRAYAGG(
        JSON_OBJECT(
            'user_name', u.user_name,
            'user_permission', u.user_permission
        )
    ) AS serialized_users
FROM DOCINF d
LEFT JOIN UDINF u ON d.doc_id = u.doc_id
GROUP BY d.doc_id, d.doc_title, d.created_date;

Example 2: Serialize into a Comma-Separated String (All Databases) #

If you just need a simple concatenated list of user names per document:

SELECT
    d.doc_id,
    d.doc_title,
    GROUP_CONCAT(u.user_name SEPARATOR ', ') AS user_list
FROM DOCINF d
LEFT JOIN UDINF u ON d.doc_id = u.doc_id
GROUP BY d.doc_id, d.doc_title;

Note: For PostgreSQL, use STRING_AGG(u.user_name, ', ') instead of GROUP_CONCAT.

Example 3: Nested JSON Serialization (PostgreSQL) #

For more robust nested JSON structures in PostgreSQL:

SELECT
    d.doc_id,
    d.doc_title,
    json_agg(row_to_json(u)) AS serialized_users
FROM DOCINF d
LEFT JOIN UDINF u ON d.doc_id = u.doc_id
GROUP BY d.doc_id, d.doc_title;

If your table structure or desired output doesn’t match these examples, share:

• The exact schema of DOCINF and UDINF (run DESCRIBE DOCINF; and DESCRIBE UDINF; in your database and paste the results)
• A sample of your expected output (e.g., what a single row should look like after serialization)

I’ll tweak the query to fit your exact needs!

内容的提问来源于stack exchange，提问作者Asri Ghani