psql - Retrieving json data that contains special characters -

-

it looks psql db contains badly formatted json fields, hence cannot retrieve data using ->> operators.

i have table column "reputation" of type json.

i perform query "reputation" column specific object:

select reputation hashes sha1='c1b5684e132a85d18a35ddb98233cc2b71efcf0e'

i receive following result looks json formatted escape characters:

"{\"status\": \"malicious\", \"scanner_match\": 33, \"first_seen\": \"2010-05-27t09:00:27\", \"scanner_count\": 34, \"last_seen\": \"2010-05-27t09:00:27\"}"

however, when i'm trying specific field in json, receive nothing:

select reputation->>status hashes sha1='c1b5684e132a85d18a35ddb98233cc2b71efcf0e'

i tried following query check how psql handles json , received error:

select * json_each((select reputation hashes sha1='c1b5684e132a85d18a35ddb98233cc2b71efcf0e')); error:  cannot deconstruct scalar

so looks psql not recognize json. if so, have couple of questions:

  1. if invalid json format, how possible psql allows insert badly formatted json json type column?
  2. is possible somehow access these json fields in existing state (maybe using special characters in names)?

its backslashes. psql see them escape characters , insert json data type no problem.

view in sql string seen string , not json, need perform string functions remove erroneous characters. 

with cte as( select cast ('"{\"status\": \"malicious\", \"scanner_match\": 33, \"first_seen\": \"2010-05-27t09:00:27\", \"scanner_count\": 34, \"last_seen\": \"2010-05-27t09:00:27\"}"'as json)  string)  select cast(ltrim(rtrim(replace(cast(string text),'\',''),'"'),'"')as json) cte

this returns json datatype


Comments

Post a Comment

Popular posts from this blog

Spring Boot + JPA + Hibernate: Unable to locate persister -

-
i trying work entity inside request method of spring boot application. entity in project , pom.xml has dependencies it. why error message?: org.hibernate.unknownentitytypeexception: unable locate persister: com.ric.bill.model.bs.par package com.ric.web; import java.util.concurrent.atomic.atomiclong; import javax.persistence.entitymanager; import javax.persistence.persistencecontext; import net.sf.ehcache.cachemanager; import net.sf.ehcache.management.cache; import net.sf.ehcache.management.cachestatistics; import org.hibernate.stat.statistics; import org.springframework.beans.factory.annotation.autowired; import org.springframework.boot.autoconfigure.domain.entityscan; import org.springframework.cache.annotation.enablecaching; import org.springframework.transaction.annotation.enabletransactionmanagement; import org.springframework.transaction.annotation.propagation; import org.springframework.transaction.annotation.transactional; import org.springframework.web.bind.annotati
Read more

go - Golang: panic: runtime error: invalid memory address or nil pointer dereference using bufio.Scanner -

-
i implementing go program uses bufio.scanner , bufio.writer have packaged code follows package main import ( "fmt" "player/command" "strings" ) func main() { //enter code here. read input stdin. print output stdout commands.scanner.scan() { //scan new line , send comand variable check command exist or not input := strings.split(strings.trim(commands.scanner.text(), " "), " ") command := input[0] if command == "" { fmt.printf("$ %s:", commands.pwd) continue } if !commands.commands[command] { commands.throwerror("cannot recognize input.") } else { commands.execute(command, input[1:], nil) } fmt.printf("$ %s:", commands.pwd) } } i using init.go file in main package follows package main import ( "flag" "player/sourc
Read more

c - double free or corruption (fasttop) -

-
this code reads null terminated string file (file containing null terminated strings & should provide index wanted string). error: *** error in `./main': double free or corruption (fasttop): 0x090a4a80 *** aborted code: char *tmp_realloc=null; char *sstring=null; int i=1; /*set file pointer point string*/ fseek(fh,index,seek_set); sstring=(char*)malloc(sizeof(char)); if(sstring == null) return null; tmp_realloc=sstring; while( (*(sstring+i-1)=(char)fgetc((file*)fh)) != 0x0 ) { /*reallocate more memory*/ tmp_realloc=(char*)realloc((char*)sstring,++i); /*if not same address copy old new & set old point new*/ if(tmp_realloc != sstring){ strcpy((char*)tmp_realloc,(char*)sstring); free(sstring); sstring=tmp_realloc; } } could tell me pointer causing issue (& reason) ? remove part /*if not same address copy old new & set old point new*/ if(tmp_realloc != sstring){ strc
Read more