-1

I would like to kindly ask for your help regarding the JSON Data on MySQL.

[
  {"type" : "PET", "rate_period" : "MONTHLY", "amount" : 100.00, "included" : false},
  {"type" : "PARKING", "rate_period" : "MONTHLY", "amount" : 150.00, "included" : false}
]

Here is the form of the data and I would like to extract the info for each category (type, rate_period, amount). Keep in mind that I am working on Metabase and does not recognize the Commands (SPLIT_PART, JSON_EXTRACT).

Any idea?

Thanks in advance!

What I actually tried:

SELECT p.deal_info::json->>'{}')::json->>'type'
FROM deals

Code on SQL where I just get the Column

Shadow
  • 33,525
  • 10
  • 51
  • 64
stefanos_1992
  • 1
  • 2
  • You tag a question for MySQL, but use the syntax specific for PostgreSQL. Check your DBMS carefully. – Akina Dec 07 '22 at 13:49
  • Hello! Thanks for the comment and the edit. Actually, the data exist on metabase in the Form that I show you above. You can edit them with PostgreSQL or MySQL. – stefanos_1992 Dec 07 '22 at 13:52
  • Let's assume that you'd use pure SQL. Show desired result for shown source query. – Akina Dec 07 '22 at 13:54
  • I added a photo with the Data and the code. I just use a select to bring them. – stefanos_1992 Dec 07 '22 at 13:59

1 Answers1

0
CREATE TABLE test
SELECT 1 id, CAST('
[
  {"type" : "PET", "rate_period" : "MONTHLY", "amount" : 100.00, "included" : false},
  {"type" : "PARKING", "rate_period" : "MONTHLY", "amount" : 150.00, "included" : true}
] 
' AS JSON) data;
SELECT id, CAST(data AS CHAR) FROM test;

SELECT id, jsontable.*
FROM test
CROSS JOIN JSON_TABLE(test.data,
                      '$[*]' COLUMNS ( type VARCHAR(255) PATH '$.type', 
                                       rate_period VARCHAR(255) PATH '$.rate_period', 
                                       amount DECIMAL(10,2) PATH '$.amount', 
                                       included BOOLEAN PATH '$.included'
                                       )) jsontable
id type rate_period amount included
1 PET MONTHLY 100.00 0
1 PARKING MONTHLY 150.00 1

fiddle

Akina
  • 39,301
  • 5
  • 14
  • 25
  • Thanks for your help! Metabase does not recognize the COLUMNS command and I have to think of a workaround. However, your way seems very viable. – stefanos_1992 Dec 07 '22 at 14:36
  • @stefanos_1992 in this case the mysql tag cannot apply to your question as this answer follows mysql syntax. – Shadow Dec 07 '22 at 18:27
  • @stefanos_1992 *Metabase does not recognize the COLUMNS command* According the documentation ".. Metabase sends the query to your database exactly as it is written." And, as the fiddle proves, the query is successfully processed by MySQL. You use incorrect method for the query execution obviously. – Akina Dec 07 '22 at 18:49