1

I have data in a jsonb column that looks like this...

{
    "1": {
        "Answer": "Incorrect:No"
    },
    "2": {
        "Answer": "Correct:The troubleshooting steps are correct",
        "Comment": "Computer was not restarted."
    },
    "3": {
        "Answer": "Correct:The actions taken were correct"
    },
    "4": {
        "Answer": "Correct:Clear next steps were provided.",
        "Comment": "Followup on fixing this issue."
    }
}

What I want to do is get a count by question (1-4) of how many records have start with "Incorrect". I have the following query...

SELECT Count(reviews) FROM reviews WHERE review->'1'->>'Answer' LIKE 'Incorrect:%'

This will give me a count for that one question but I don't want to have 4 queries if I can help it. I've tried...

SELECT 
Count(review->'1'->>'Answer' LIKE 'Incorrect:%') AS "Count1",
Count(review->'2'->>'Answer' LIKE 'Incorrect:%') AS "Count2"
FROM reviews;

But that counted all columns. Any ideas?

S-Man
  • 22,521
  • 7
  • 40
  • 63
James
  • 191
  • 11

1 Answers1

0

demo:db<>fiddle (Note that I made answer 4 incorrect as well for testing purposes)

SELECT 
    COUNT(*) 
FROM mytable,
    json_each(mydata) as data                  
WHERE data.value ->> 'Answer' LIKE 'Incorrect%'

json_each() extracts all JSON elements into an own row. This can be used to check them separately and group their results afterwards.

S-Man
  • 22,521
  • 7
  • 40
  • 63
  • @James: Did this help? If not, please tell us, what to change. If it helped in any way, please UPVOTE the question (which honors the time and effort each replier invested into your problem). If it solved your problem completely, please ACCEPT the answer additionally to close the question. – S-Man Jan 25 '21 at 14:53