如何移除表格中括号与引号？JSON类型uuid列处理方案问询

Hey there! Let's work through your UUID column processing needs step by step, and also offer a more streamlined approach since you're ultimately using NetworkX in Python.

Option 1: Process directly in SQL before exporting to CSV #

If you want to handle the formatting in your database (e.g., MySQL, PostgreSQL), you can use string manipulation functions to strip brackets, remove quotes, and re-wrap the values. Here's how to do it for common databases:

For MySQL 8.0+

SELECT 
  CONCAT('[', REPLACE(TRIM(BOTH '[]' FROM uuid), '"', ''), ']') AS processed_uuid
FROM your_table;

• TRIM(BOTH '[]' FROM uuid) strips the opening and closing brackets from the JSON string
• REPLACE(..., '"', '') removes all double quotes from the remaining value list
• CONCAT('[', ..., ']') adds the brackets back to get your desired format

For PostgreSQL

You can use a combination of JSON functions and string operations for safer parsing:

SELECT 
  CONCAT('[', array_to_string(ARRAY(SELECT json_array_elements_text(uuid)), ', '), ']') AS processed_uuid
FROM your_table;

This method extracts each element from the JSON array first, then joins them into a comma-separated string before wrapping in brackets (avoids issues with edge cases like escaped quotes).

Once you run this query, export the result as a CSV file as you normally would.

Option 2: Process in Python (better for NetworkX compatibility) #

Since you're already planning to use Python's NetworkX, it's often more reliable to export the raw JSON UUID column first, then parse and process it directly in Python. This avoids potential issues with string manipulation in SQL (like unexpected characters in JSON).

Here's a step-by-step code example:

import json
import pandas as pd
import networkx as nx

# Load the raw CSV with your JSON UUID column
df = pd.read_csv("raw_input.csv")

# Parse the JSON strings into Python lists (no quotes needed here—they're already stripped!)
df["uuid_nodes"] = df["uuid"].apply(json.loads)

# Export the processed data to CSV if you still need it
df.to_csv("processed_output.csv", index=False)

# Directly build your NetworkX graph using the parsed lists
G = nx.Graph()
for node_list in df["uuid_nodes"]:
    # Example: Connect all nodes in each list sequentially (adjust based on your graph logic)
    if len(node_list) >= 2:
        for i in range(len(node_list) - 1):
            G.add_edge(node_list[i], node_list[i+1])

# Verify your graph
print(f"Graph has {G.number_of_nodes()} nodes and {G.number_of_edges()} edges")

This approach is cleaner because:

• json.loads() safely parses the JSON array into a Python list of UUID strings (no quotes left!)
• You can directly use these lists to build your NetworkX graph without extra formatting steps
• It's more robust if your JSON arrays ever have edge cases (like escaped characters)

A quick note on your original approach: Using JSON_EXTRACT(uuid, '$[0]') only gets the first element, which is why it didn't work for multi-value arrays. The methods above handle all elements in each array at once.

内容的提问来源于stack exchange，提问作者Rishabh Sahrawat