• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

mySql select statement with returning JSON data types via Java

 
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So I have this test MySQL db with a table test1 with JSON datatype our_data:

'{\"cars\": {\"chev\": \"bolt\", \"ford\": \"explorer\", \"jeep\": \"ranger\"}}'

I can connect successfully to the base on if a select without JSON_KEYS

     

return a string, however if I add a JSON_KEYS

     

I get:

SQL Connection to database established!
Connection Failed! Check output console
Column 'our_data' not found., 0

I'm pretty sure it's because JSON_KEYS(our_data) does not return a String Object!

I'm thinking I've got some parsing to do. But what return type does the select with JSON_KEYS return? And how does one parse an JSON OBJECT from a MySQL DB?
Is there a sassy myRs.getJSONTYPETHING("our_data") that returns the correct object? Do I have to cast the returned value? A million questions?

This completely new to me and I'm just testing whether it's a good idea to include JSON OBJECTS in a MySQL DB.

Ideas? Thoughts? Solutions? Directions? Bad Idea?!





 
Marshal
Posts: 4491
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
   - Are you running MySQL version 5.7 or higher?
   - Does your column have a datatype of JSON?
   - Have you tried running the query from the command line?
 
Mike Parish
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Running MySQL Version 8.0.15.
Created the table with command "create table test1 (our_data JSON)";
I can use all JSON commands (JSON_PRETTY, JSON_KEYS, JSON_CONTAINS_PATH, etc) from the command line.
 
Ron McLeod
Marshal
Posts: 4491
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Please post the output of select JSON_KEYS(our_data) from test1 when run from the command line.

Does it have a column heading of our_data ?
 
Mike Parish
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
mysql> select our_data from test1;


+------------------------------------------------------------------+
| our_data                                                         |
+------------------------------------------------------------------+
| {"cars": {"chev": "bolt", "ford": "explorer", "jeep": "ranger"}} |
+------------------------------------------------------------------+
1 row in set (0.00 sec)



mysql> select JSON_KEYS(our_data) from test1;
+---------------------+
| JSON_KEYS(our_data) |
+---------------------+
| ["cars"]            |
+---------------------+
1 row in set (0.00 sec)


Return from the command line run on Terminal on my Mac.

It fails using Java.
 
Ron McLeod
Marshal
Posts: 4491
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Mike Parish wrote:mysql> select JSON_KEYS(our_data) from test1;
+---------------------+
| JSON_KEYS(our_data) |
+---------------------+
| ["cars"]            |
+---------------------+
1 row in set (0.00 sec)



It fails using Java.


It is failing because in your code, you are specifying a column name of our_data, but the result is using a column name of JSON_KEYS(our_data)
Three possible fixes:
   - change your code to specify column name of JSON_KEYS(our_data): myRs.getString("JSON_KEYS(our_data)")
   - change your code you specify a column number rather than name: myRs.getString(1)
   - change your query to return a column name of our_data: select JSON_KEYS(our_data) AS our_data from test1;
 
Mike Parish
Greenhorn
Posts: 26
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So, following your advise:




Tada Result:

Doc is: ["cars"] <- exactly what I see from the command line.

Once you see it, it makes so much sense. I think it's time to practise select/insert/delete/update commands in MySQL.

Thanks for all your help.
 
Ron McLeod
Marshal
Posts: 4491
572
VSCode Eclipse IDE TypeScript Redhat MicroProfile Quarkus Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Great.  Thanks for reporting back on your success.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic