0

Query 1:

select 
 distinct ifnull(concat(UPPER(JSON_EXTRACT_SCALAR(Profiles.DATA,'$.firstName')) , ' ', upper(JSON_EXTRACT_SCALAR(Profiles.DATA,'$.lastName'))),"-")  AS VendorName,
        ifnull(UPPER(JSON_EXTRACT_SCALAR(Product.DATA, '$.category')),"-") as productCategory
        from
        `roque-prod.profiles.profiles_raw_latest` as Profiles , `roque-prod.roqueprod.roque_raw_latest` as Product
        where
        Profiles.document_id = JSON_EXTRACT_SCALAR(Product.Data,'$.author')

Query 2 :

select 
 distinct ifnull(concat(UPPER(JSON_EXTRACT_SCALAR(Profiles.DATA,'$.firstName')) , ' ', upper(JSON_EXTRACT_SCALAR(Profiles.DATA,'$.lastName'))),"-")  AS VendorName,
                    ifnull(UPPER(JSON_EXTRACT_SCALAR(Services.DATA, '$.category')),"-") as ServicesCategory
                    from
                    `roque-prod.profiles.profiles_raw_latest` as Profiles , services.services_raw_latest as Services
                    where
                    Profiles.document_id = JSON_EXTRACT_SCALAR(Services.Data,'$.author')

After joining I want to get 3 columns , 1)VendorName 2)ProductCategory 3)ServiceCategory which are group by VendorName.

bhakti vyas
  • 1
  • 1
  • MySQL does not know about `JSON_EXTRACT_SCALAR` function. Adjust tags list for the question. – Akina Oct 12 '20 at 05:36
  • I am using MYSQL simple queries but to retrieve the data i have to use json_extract_scalar , as data is stored in json string format. – bhakti vyas Oct 12 '20 at 05:38
  • And these 2 queries are perfectly running when they are executed individually, I want to get them in one query. – bhakti vyas Oct 12 '20 at 05:39
  • [MySQL 8.0 Reference Manual / ... / JSON Function Reference](https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html) - this is COMPLETE list of JSON functions supported by MySQL. `JSON_EXTRACT_SCALAR` is absent in it. Dixi. – Akina Oct 12 '20 at 05:41

0 Answers0