Create json document from SQL Server query

Welcome Forums General PowerShell Q&A Create json document from SQL Server query

Viewing 6 reply threads
  • Author
    Posts
    • #243230
      Participant
      Topics: 6
      Replies: 8
      Points: 39
      Rank: Member

      Hi, I am working on creating json document from SQL Server query and save the resulting json resultset as a json document on a physical drive. I am able to generate json data but not sure how to save results as result.json to the drive “C:\jsonDocs\result.json”.

      Thank you.

      • This topic was modified 2 months, 1 week ago by venkatesh SQL. Reason: format
      • This topic was modified 2 months ago by venkatesh SQL. Reason: format
    • #243266
      Participant
      Topics: 0
      Replies: 4
      Points: 46
      Rank: Member

      Simply add a call to Set-Content  “C:\jsonDocs\result.json”. after your convertTo-Json

       

      e.g.

      Alternatively, you can save your json to an object and then save the content as a separate step

      e.g.

      one caution, if your data has multiple levels (unlikely in this case) then you may need to add a -Depth parameter to your ConvertTo-Json.

    • #243749
      Participant
      Topics: 6
      Replies: 8
      Points: 39
      Rank: Member

      Hi Justin M, Thanks very much for your response. The solution works.

      Also if I were to do the same call an api (GET) from powershell. How to save the results of json payload.

      For instance, when I call the api http://myapi.com/v1/weatheradata. This gives me the below json payload.

      I would now have this saved as “C:\jsonDocs\weather.json”.

      {4 items<br />"message":"accurate"<br />"cod":"200"<br />"count":1<br />"list":[1 item<br />0:{11 items<br />"id":2643743<br />"name":"London"<br />"coord":{2 items<br />"lat":51.5085<br />"lon":-0.1257<br />}<br />"main":{6 items<br />"temp":291.57<br />"feels_like":288.33<br />"temp_min":290.37<br />"temp_max":292.59<br />"pressure":1024<br />"humidity":42<br />}<br />"dt":1595275779<br />"wind":{2 items<br />"speed":3.1<br />"deg":10<br />}<br />"sys":{1 item<br />"country":"GB"<br />}<br />"rain":NULL<br />"snow":NULL<br />"clouds":{1 item<br />"all":7<br />}<br />"weather":[1 item<br />0:{4 items<br />"id":800<br />"main":"Clear"<br />"description":"clear sky"<br />"icon":"01n"<br />}<br />]<br />}<br />]<br />}

      Regards.

    • #243794
      Participant
      Topics: 0
      Replies: 4
      Points: 46
      Rank: Member

      you have several options, if you want to save the raw json, its the same

       

      if you want you can also convert it to an object:

       

      you can also save this native “object” using

      and then can re-import using

      and you can convert that to json using:

      where ## is an appropriate depth for the complexity of the object.

    • #244376
      Participant
      Topics: 6
      Replies: 8
      Points: 39
      Rank: Member

      Hi Justin M, Thank you for your response. I am using the below. Please let me know if you see any issues with it.

      [/crayon]

      Regards

      • This reply was modified 2 months ago by venkatesh SQL. Reason: typo
    • #244394
      Participant
      Topics: 0
      Replies: 4
      Points: 46
      Rank: Member

      i’m not familiar with bearer tokens myself, however if this is a rest method, you would better to use the Invoke-RestMethod API

       

      if using PS6+, per the documentation:

      -Token

      The OAuth or Bearer token to include in the request. Token is required by certain Authentication options. It can’t be used independently.

      Token takes a  SecureString that contains the token. To supply the token, manually use the following:

      Invoke-RestMethod -Uri $uri -Authentication OAuth -Token (Read-Host -AsSecureString)

      This parameter was introduced in PowerShell 6.0.

      Type: SecureString

      note the use of SecureString, in this example it prompts but if you ahve it in a variable you may need to convert it using:

       

      if using PS < 5, per the documentation

      https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/invoke-restmethod?view=powershell-5.1

      This example demonstrates, how to pass multiple headers in from a  hash-table to a REST API.

    • #244400
      Participant
      Topics: 6
      Replies: 8
      Points: 39
      Rank: Member

      Thanks very much Justin M. I modified my code as well in the previous post.

      Regards

Viewing 6 reply threads
  • You must be logged in to reply to this topic.