Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Is the vertica has the function that converts row to JSON format.?

edited March 22 in General Discussion

Is the vertica has the function that converts row to JSON format.?

  • The table is not a flex table*
    example)
    select * from table;
    ** general result**
    id text
    1 amy
    2 john

if i use the json function
select json(*) from table;
** json result **

result
{'id':1, 'text':'amy'}
{'id':2, 'text':'john'}

Answers

  • SergeBSergeB Employee

    You can use the vmap functions to build a vmap for each row and then get its JSON representation.

     select maptoString(mapput(emptymap(),id,"text" using parameters keys=SetMapKeys('id','text'))) as json_string from foo;
                  json_string
    ---------------------------------------
     {
        "id": "1",
          "text": "amy"
    }
     {
        "id": "2",
        "text": "john"
    }
    
  • Oh.. Thank you so much.. @SergeB
    I have one more question..
    If i need all column,, then, i have to write all column in the function??

  • SergeBSergeB Employee

    @HyeontaeJu Yes, you will need to list all the columns you wish to include in your JSON result.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.