Fetching json content of a File in SharePoint Library

Question

I have started with the following code to retrieve the List Items for JSON files found in my document library:

Try {
    #Connect to PNP Online
    Connect-PnPOnline -Url $siteUrl -UseWebLogin
    $CAMLQuery1 = "<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='FileLeafRef'></FieldRef>
                <Value Type='Text'>content.json</Value>
            </Eq>
        </Where>
    </Query></View>"
    $FolderItems = Get-PnPListItem -List $ListName -Query $CAMLQuery1
    ForEach($Item in $FolderItems)
    {
        Write-host $Item
    }

}
catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

How do I get the json content of the file I fetched through the CAML request? I tried to find some solution, but couldn't find anything.

Answer 1

As far as I know, there is not yet a specific PnP cmdlet to help with this. The trick is that SharePoint doesn't care that the contents of your file are JSON or plain Text, it still stores it as a binary file. So you have to retrieve it as a binary stream, then read that stream into a string variable. Then finally, if you want an actual PowerShell object from that JSON string, you'll need to use the ConvertFrom-Json cmdlet.

The following should get you what you need:

Try {
    #Connect to PNP Online
    Connect-PnPOnline -Url $siteUrl -UseWebLogin
    $ctx = get-pnpContext
    $CAMLQuery1 = "<View Scope='RecursiveAll'>
    <Query>
        <Where>
            <Eq>
                <FieldRef Name='FileLeafRef'></FieldRef>
                <Value Type='Text'>content.json</Value>
            </Eq>
        </Where>
    </Query></View>"
    $FolderItems = Get-PnPListItem -List $ListName -Query $CAMLQuery1
    ForEach($Item in $FolderItems)
    {
        Write-host $Item

        $fileinfo = [Microsoft.SharePoint.Client.File]::OpenBinaryDirect($ctx, $Item["FileRef"]) #ServerRelativeUrl
        $sr = new-object System.IO.StreamReader($fileInfo.Stream)

        $textcontent = $sr.ReadToEnd()      
        $jsonObject = ConvertFrom-Json -InputObject $textcontent

        $sr.Dispose()

        # do your actions with the $textcontent or #jsonOjbect here
    }

}
catch {
    write-host "Error: $($_.Exception.Message)" -foregroundcolor Red
}

Answer 2

You can refer the below PowerShell script :

$listArray = New-Object  System.Collections.Generic.List[System.Object] 
foreach ($item in $ListItems)
    {
 $listArray.Add([hashtable]@{ DisplayTitle=$item["Title"]; 
Setting= $item["Setting"]; 
Description= $item["File_x0020_Description"]; 
HealthAreas= $item["Health_x0020_Area"];
 ResourceType= $item["Resource_x0020_Type"]; 
Active= $item["Currently_x0020_active_x003f_"]; 
ExternalURL= $item["External_x0020_file_x0020_path"]; 
} ) } 
$json = $listArray | ConvertTo-Json Write-Host $json

Reference URL :

How to GetListItems as JSON using Powershell with SharePoint Online?