CODEWITHSUNDEEP
mysqljson
Chest Rockwell
Chest Rockwell

Reputation: 177

Inserting missing range of values in json data

I am querying a mysql database to return the number of times a certain pain score has been entered by a user. The query looks like this:

SELECT 
    User,
    CAST(Value as UNSIGNED)as Pain_Score, 
    COUNT(Value) as Pain_Score_Count
FROM
    pain_score
WHERE
    Text = 'Pain Score'
AND
    User = :user
GROUP BY 
    Value
ORDER BY
    Pain_Score
ASC

Table schema:

Field   Type
id      int(10)
Text    varchar(250)
User    varchar(255)
Value   varchar(15000)

The query is returning data in json format that looks like this:

[
    {
        "User": "Test, User",
        "Pain_Score": "0",
        "Pain_Score_Count": "6"
    },
    {
        "User": "Test, User",
        "Pain_Score": "5",
        "Pain_Score_Count": "4"
    },
    {
        "User": "Test, User",
        "Pain_Score": "6",
        "Pain_Score_Count": "3"
    },
    {
        "User": "Test, User",
        "Pain_Score": "7",
        "Pain_Score_Count": "1"
    },
    {
        "User": "Test, User",
        "Pain_Score": "8",
        "Pain_Score_Count": "4"
    }
]

But for any given set of data returned I need a Pain_Score range from 0 to 10, ie:

[
        {
            "User": "Test, User",
            "Pain_Score": "0",
            "Pain_Score_Count": "6"
        },
        {
            "User": "Test, User",
            "Pain_Score": "1",
            "Pain_Score_Count": "0"
        },
        {
            "User": "Test, User",
            "Pain_Score": "2",
            "Pain_Score_Count": "0"
        },
        {
            "User": "Test, User",
            "Pain_Score": "3",
            "Pain_Score_Count": "0"
        },
        {
            "User": "Test, User",
            "Pain_Score": "4",
            "Pain_Score_Count": "0"
        },
        {
            "User": "Test, User",
            "Pain_Score": "5",
            "Pain_Score_Count": "4"
        },
        {
            "User": "Test, User",
            "Pain_Score": "6",
            "Pain_Score_Count": "3"
        },
        {
            "User": "Test, User",
            "Pain_Score": "7",
            "Pain_Score_Count": "1"
        },
        {
            "User": "Test, User",
            "Pain_Score": "8",
            "Pain_Score_Count": "4"
        },
        {
            "User": "Test, User",
            "Pain_Score": "9",
            "Pain_Score_Count": "0"
        },
        {
            "User": "Test, User",
            "Pain_Score": "10",
            "Pain_Score_Count": "0"
        }
    ]

Is there a way to return the missing range of Pain_Score values by editing the query or is there a simple way to insert the missing values for a random set of given Pain_Score values?

Upvotes: 0

Views: 48

Answers (1)

Sunny Patel
Sunny Patel

Reputation: 8077

You'll need to synthesize all the numbers you're wanting to include and join on that. Here's one way to do it. DBFiddle

SELECT
    User,
    num as Pain_Score,
    COUNT(Value) as Pain_Score_Count
FROM
    pain_score
RIGHT JOIN (select 0 num union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) nums
    ON num = Value
    AND User = "Test, User"
    AND Text = "Pain Score"
GROUP BY 
    num, User
ORDER BY num ASC

In this example, I've hardcoded the user, but you should be able to adjust the SELECT and JOIN statements to replace User and "Test, User" with your :User variable, respectively.

Upvotes: 1

Related Questions