I apologize if this is a simple question - I had some trouble even formatting the question when I was trying to Google for help!

In one of the tables I am working with, there's data value that looks like below:

I want to count how many products each order has purchased. What is the proper syntax and way to count the values under "Product List" column? I'm aware that count() is wrong, and I need to maybe extract the data from the string value.

select invoice_id, count(Product_list) 

from quote_table

where status = 'processed'

group by invoice_id

CodePudding user response：

You can use a JSON function named: json_array_length and cast this column like a JSON data type (as long as possible), for example:

select invoice_id, json_array_length(Product_list::json)  as count

from quote_table

where status = 'processed'

group by invoice_id;


 invoice_id | count 
------------ -------
     1234   |     4
(1 row)

CodePudding user response：

If you need to count a specific property of the json column, you can use the query below.

This query solves the problem by using create type, json_populate_recordset and subquery to count product_id inside json data.

drop type if exists count_product;

create type count_product as (product_id int);

select 
    t.invoice_id, 
    t.status,
    (
        select count(*) from json_populate_recordset(
            null::count_product, 
            t.Product_list
        ) 
        where product_id is not null    
    ) as count_produto_id
from (
    -- symbolic data to use in query
    select 
        1234 as invoice_id, 
        'processed' as status, 
        '[{"product_id":463153},{"product_id":463165},{"product_id":463177},{"pid":463218}]'::json as Product_list 
) as t