0

This query works perfect

SELECT * FROM application_info WHERE id IN (20002, 20001)

However when I add FOR JSON PATH I get a json that is invalid and the data is incomplete.

SELECT * FROM application_info WHERE id IN (20002, 20001) FOR JSON PATH

An example of the json I am getting back is below. Notice how it just ends abruptly. Obviously the FOR JSON PATH is causing the error as the query without it works just fine. What am I doing wrong?

[{
    "id": 20002,
    "app_status": "Active",
    "true_up": "Yes"
}, {
    "id": 20001,
    "app_status": "Active",
    "true_up": "Yes"
}, {
    "id": 20002,
    "app_status": "Inactive",
    "true_up": "Yes"
}, {
    "id": 20002,
    "app_status": "Active",
    "true_up": "Yes"
}, {
    "id": 20002,
    "app_status": "Inactive",
    "true_

Sample table data application_info

id         app_status      true_up
-----------------------------------
20003       Active          Yes
20002       Active          Yes
20004       Active          Yes
20003       Active          Yes
20001       Active          Yes
20002       Active          Yes
20002       InActive        Yes
20003       Active          Yes
20005       InActive        Yes
20002       Active          Yes
20002       Active          Yes
20003       Active          Yes
20001       Active          Yes
20002       Active          Yes
Jam1
  • 629
  • 12
  • 25
  • Please provide sample data so we can replicate this behavior. – GMB Aug 25 '20 at 23:28
  • Ok will do add that ASAP – Jam1 Aug 25 '20 at 23:29
  • 1
    Are you checking the length of the JSON output in SSMS? It has a limit of how much characters it outputs for XML and JSON constructs. Try to assign the output to a `nvarchar(max)` variable and check its length, such as `select len(@myjsonvar);`. – Roger Wolf Aug 25 '20 at 23:46
  • No I am not checking the limit. Mhhh...this seems like it could be the issue. Will do what you say. – Jam1 Aug 25 '20 at 23:48
  • The length of the output is `129507`, I did it with just id =`20002` and got a different cut off point with length `67875`. I think it is the `json` length. – Jam1 Aug 25 '20 at 23:54
  • 1
    Did it with `TOP 50` and got correct `json`. I will look into a way to get all the results. – Jam1 Aug 26 '20 at 00:00

0 Answers0