PowerShell：如何从SQLite字段的JSON中查找指定对象并遍历修改

前置准备 #

首先安装PowerShell的SQLite模块（需管理员权限）：

Install-Module -Name Sqlite -Scope CurrentUser -Force

完整处理流程 #

# 指定数据库路径
$dbPath = "C:\path\to\your\target.db"
# 建立数据库连接
$conn = Open-SqliteConnection -DataSource $dbPath

# 查询包含目标JSON字段的记录（假设表名为`device_table`，JSON字段名为`payload`）
$fetchQuery = "SELECT id, payload FROM device_table"
$records = Invoke-SqliteQuery -Connection $conn -Query $fetchQuery

foreach ($record in $records) {
    # 将JSON字符串解析为PowerShell可操作的对象
    $jsonData = $record.payload | ConvertFrom-Json

    # 筛选符合条件的items：location为null 且 modelname不等于"Model3"
    $targetItems = $jsonData.items | Where-Object {
        $null -eq $_.location -and $_.modelname -ne "Model3"
    }

    # 遍历目标items修改location属性
    foreach ($item in $targetItems) {
        $item.location = "Default_Location" # 替换为你需要设置的具体值
    }

    # 将修改后的对象转回JSON字符串，-Depth确保嵌套结构不被截断（根据实际嵌套层级调整数值）
    $updatedJson = $jsonData | ConvertTo-Json -Depth 10

    # 执行数据库更新，用参数化查询避免SQL注入风险
    $updateQuery = @"
UPDATE device_table
SET payload = @updatedJson
WHERE id = @recordId
"@
    Invoke-SqliteQuery -Connection $conn -Query $updateQuery -Parameters @{
        updatedJson = $updatedJson
        recordId = $record.id
    }
}

# 关闭数据库连接
Close-SqliteConnection -Connection $conn

关键细节说明 #

• JSON解析与序列化：ConvertFrom-Json把JSON字符串转成PowerShell对象，ConvertTo-Json反向转换，必须指定-Depth参数（默认值2），否则深层嵌套的JSON结构会被截断。
• 空值判断：PowerShell中判断空值要写$null -eq $_.location，而不是$_.location -eq $null，避免因对象不存在导致的逻辑错误。
• 参数化查询：更新数据库时用参数绑定，不要直接拼接字符串，防止SQL注入问题。

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