• 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Ron McLeod
  • Tim Cooke
Sheriffs:
  • Devaka Cooray
  • paul wheaton
  • Mark Herschberg
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Stephan van Hulst
  • Frits Walraven
  • Jj Roberts
Bartenders:
  • Carey Brown
  • salvin francis
  • Piet Souris

How do i retrieve both the key and the value from a json object?

 
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am trying to retrieve both parts of a JSON object through a stored procedure, for example from {bob:ben, 3:sue, James:cat} I want to get each part of the pair separately using sql and put them in a table. So it would be like this


How do i do this?
 
Marshal
Posts: 26381
81
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It sort of sounds to me like you want to write an SQL stored procedure to take a JSON object and convert it to rows in a database. Did I understand that correctly?
 
matthew irving
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thats right i need to take both the key and the value from each pair and and put them into two columns. I'm having the most trouble with taking the json input and turning it into a variable i can work with.
 
Marshal
Posts: 3408
493
Android 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

matthew irving wrote:for example from {bob:ben, 3:sue, James:cat}


That isn't valid JSON - the data should be formed like:
If that data represents a collection of key-value pairs rather than an object, then this is probably a better representation:
 
Ron McLeod
Marshal
Posts: 3408
493
Android 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

Paul Clapham wrote:It sort of sounds to me like you want to write an SQL stored procedure to take a JSON object and convert it to rows in a database ...


matthew irving wrote:Thats right i need to take both the key and the value from each pair and and put them into two columns. I'm having the most trouble with taking the json input and turning it into a variable i can work with.


Do you have to do this in a database stored procedure, or do you have the option of doing this work in application code?
 
matthew irving
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
It has to be a stored procedure.
 
Paul Clapham
Marshal
Posts: 26381
81
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I searched the web and found that (to my surprise and then not surprised) several databases have built-in functions to convert a JSON array to rows in a database table. Your database may or may not be one of them, but searching its documentation should clear that up fairly quickly.
 
matthew irving
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How do i retrieve the value of the input and convert it to a variable?
 
Paul Clapham
Marshal
Posts: 26381
81
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I don't understand that question at all. I don't know what "the input" means and I don't know what "a variable" means in your context. Perhaps you could clarify by showing us what you have written so far in your stored procedure, that might explain things.
 
If a regular clown is funny, then a larger clown would be funnier. Math. Verified by this tiny ad:
the value of filler advertising in 2021
https://coderanch.com/t/730886/filler-advertising
reply
    Bookmark Topic Watch Topic
  • New Topic