PowerShell JSON adding value format

Question

I am adding data to a json file. I do this by

$blockcvalue =@" 
    {
    "connectionString":"server=(localdb)\\mssqllocaldb; Integrated Security=true;Database=$database;"
    }
"@

$ConfigJson = Get-Content C:\Users\user\Desktop\myJsonFile.json -raw | ConvertFrom-Json

$ConfigJson.data | add-member -Name "database" -value (Convertfrom-Json $blockcvalue) -MemberType NoteProperty

$ConfigJson | ConvertTo-Json| Set-Content C:\Users\user\Desktop\myJsonFile.json

But the format comes out like this:

{
    "data":  {
                    "database":  {
                                 "connectionString":  "server=(localdb)\\mssqllocaldb; Integrated Security=true;Database=mydatabase;"
                             }
                }
}

but I need it like this:

{
    "data": {
    "database":"server=(localdb)\\mssqllocaldb; Integrated Security=true;Database=mydatabase;"
    }
    }
}

Can someone help please?

Note: The different formatting between the current and the desired output led to the misconception that this question is about formatting, which I don't think it is. Instead, is about making the data.database property contain the connection string directly rather than via a nested object that has a .connectionString property. — mklement0
– mklement0, Commented Aug 2, 2019 at 18:57

Theo · Accepted Answer · 2018-06-13 09:54:39Z

Here's my function to prettify JSON output:

function Format-Json {
    <#
    .SYNOPSIS
        Prettifies JSON output.
    .DESCRIPTION
        Reformats a JSON string so the output looks better than what ConvertTo-Json outputs.
    .PARAMETER Json
        Required: [string] The JSON text to prettify.
    .PARAMETER Indentation
        Optional: The number of spaces to use for indentation. Defaults to 2.
    .PARAMETER AsArray
        Optional: If set, the output will be in the form of a string array, otherwise a single string is output.
    .EXAMPLE
        $json | ConvertTo-Json  | Format-Json -Indentation 4
    #>
    [CmdletBinding()]
    Param(
        [Parameter(Mandatory = $true, Position = 0, ValueFromPipeline = $true)]
        [string]$Json,

        [int]$Indentation = 2,
        [switch]$AsArray
    )
    # If the input JSON text has been created with ConvertTo-Json -Compress
    # then we first need to reconvert it without compression
    if ($Json -notmatch '\r?\n') {
        $Json = ($Json | ConvertFrom-Json) | ConvertTo-Json -Depth 100
    }

    $indent = 0
    $Indentation = [Math]::Abs($Indentation)
    $regexUnlessQuoted = '(?=([^"]*"[^"]*")*[^"]*$)'

    $result = $Json -split '\r?\n' |
        ForEach-Object {
            # If the line contains a ] or } character, 
            # we need to decrement the indentation level unless it is inside quotes.
            if ($_ -match "[}\]]$regexUnlessQuoted") {
                $indent = [Math]::Max($indent - $Indentation, 0)
            }

            # Replace all colon-space combinations by ": " unless it is inside quotes.
            $line = (' ' * $indent) + ($_.TrimStart() -replace ":\s+$regexUnlessQuoted", ': ')

            # If the line contains a [ or { character, 
            # we need to increment the indentation level unless it is inside quotes.
            if ($_ -match "[\{\[]$regexUnlessQuoted") {
                $indent += $Indentation
            }

            $line
        }

    if ($AsArray) { return $result }
    return $result -Join [Environment]::NewLine
}

Use it like so:

$ConfigJson | ConvertTo-Json | Format-Json | Set-Content C:\Users\user\Desktop\myJsonFile.json

Nice, though I don't think the OP's question is about formatting: it is about the structure of the output data. As an aside: ConvertTo-Json's pretty-printing format has been improved in PowerShell Core - see github.com/PowerShell/PowerShell/issues/2736

mklement0 · Accepted Answer · 2019-08-02 18:54:35Z

0

Replace

(Convertfrom-Json $blockcvalue)

with

(Convertfrom-Json $blockcvalue).connectionString

Then your output object's data.database property will directly contain the "server=(localdb)\\..." value, as desired, not via a nested object that has a connectionString property.

answered Aug 2, 2019 at 18:54

mklement0

452k68 gold badges727 silver badges986 bronze badges

Comments

user · Accepted Answer · 2020-07-14 10:13:18Z

0

There is one simple Newtonsoft.Json Parser which makes it rly simple to get required format:

Import-Module Newtonsoft.Json
$path = "C:\..."
$json = Get-Content -Path $path -Raw 
$parsedJson = [Newtonsoft.Json.Linq.JToken]::Parse($json);
Set-Content $path $parsedJson.ToString();

Enjoy ;)

edited Jul 14, 2020 at 10:13

answered Jul 14, 2020 at 7:33

user

111 silver badge4 bronze badges

Collectives™ on Stack Overflow

PowerShell JSON adding value format

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related